无法连接事实表的MDX计算度量（含IF与比较运算）需求咨询

Since Fact A and Fact B can't be joined in the data source view, but your Actual Units, Future Units, and Demand Units all share the same granularity, we can build the three required calculated measures directly in MDX. MDX will handle aligning values from both fact tables based on the current dimension context, no explicit join needed.

1. Projected Units Calculated Measure #

This measure uses MDX's CoalesceEmpty (the equivalent of standard Coalesce) to prioritize Actual Units and fall back to Future Units when the former is null:

CREATE MEMBER CURRENTCUBE.[Measures].[Projected Units]
AS 
  CoalesceEmpty(
    [Measures].[Actual Units], -- Pull from Fact A
    [Measures].[Future Units]  -- Fallback to Fact B if needed
  ),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CoalesceEmpty returns the first non-null value in the list, which perfectly matches your requirement to use actuals where available, then future values.

2. Stock Units Calculated Measure #

We use MDX's IIF function to compare Projected Units against Demand Units, plus a check for null Demand Units to avoid unexpected behavior:

CREATE MEMBER CURRENTCUBE.[Measures].[Stock Units]
AS 
  IIF(
    IsEmpty([Measures].[Demand Units]),
    [Measures].[Projected Units], -- Handle missing demand values
    IIF(
      [Measures].[Projected Units] > [Measures].[Demand Units],
      [Measures].[Demand Units],
      [Measures].[Projected Units]
    )
  ),
FORMAT_STRING = "Standard",
VISIBLE = 1;

This ensures we don't run into edge-case errors and correctly applies your rule to cap stock units at demand levels when projections exceed demand.

3. Stock Rate Calculated Measure #

For this percentage calculation, we must handle cases where Demand Units is 0 or null to avoid division errors. Adjust the fallback value (NULL vs. 0) based on your team's business rules:

CREATE MEMBER CURRENTCUBE.[Measures].[Stock Rate]
AS 
  IIF(
    IsEmpty([Measures].[Demand Units]) OR [Measures].[Demand Units] = 0,
    NULL, -- Swap with 0 if you want to display 0% instead of blank
    [Measures].[Stock Units] / [Measures].[Demand Units]
  ),
FORMAT_STRING = "Percent", -- Auto-formats values as percentages (e.g., 0.75 → 75%)
VISIBLE = 1;

Key Tips #

• Granularity Check: Since all three measures share the same granularity, MDX will automatically align their values when you slice by common dimensions (like date, product, region, etc.).
• Deployment: Add these calculated members to your cube's calculation script for permanent use, or define them inline in ad-hoc queries as needed.
• Testing: Always validate edge cases (nulls, zero demand) to ensure calculations behave as expected in all scenarios.

内容的提问来源于stack exchange，提问作者Krish Dev