Power BI DAX Measure - Summarize Across multiple Dimensions and Facts.

Hi Simeon 

Interesting scenario, and you have hit on some good ideas

If the two fact tables are aggregated to the same grain (e.g. Date/Location/SKU) you could indeed precompute the result for each row as a binary value and sum that. However it sounds like this is not always the case.

To answer your specific questions:

• To more optimally write such a measure that has to iterate over combinations of dimensions, I would generally use SUMMARIZE to group the fact table by the required dimensions (rather than CROSSJOIN), then iterate over the result. SUMMARIZE produces these comabinations very efficiently with the storage engine. Also, I would not suggest using SUMMARIZE to create extension columns (see this article).
• Your star schema looks logical and correct to me!
• Last I checked, SUMMARIZECOLUMNS cannot generally be used in measures (in particular within iterators) but it has been suggested that this is going to change (see this video featuring Jeffrey Wang).

Here is how I would suggest writing the measure, based on what you've posted, including two measures for the inventory sums that I would create for readability.

Oh, I have assumed that you would only count combinations that exist in 'Fact_Inventory_Req' - is this a correct assumption or not? If not, some adjustments would be needed.

Opening Inventory Sum =
SUM ( 'Fact_Inventory'[Opening Inventory] )

Required Inventory Sum =
SUM ( 'Fact_Inventory_Req'[Inventory_Qty_Rec] )

Measure =
-- Combinations of Date/SKU/Location existing in Fact_Inventory_Req
VAR SummarizedTable =
    SUMMARIZE (
        'Fact_Inventory_Req',
        'Dim_Date'[Date],
        'Dim_SKU'[SKU Key],
        'Dim_Location'[Location Key]
    )
VAR Result =
    SUMX (
        SummarizedTable,
        IF ( [Opening Inventory Sum] > [Required Inventory Sum], 1 )
    )
RETURN
    Result

Interested in whether this is an improvement or not. It may depend on how sparse combinations in your fact tables are.

Regards

Glad that was some help 🙂

For the multi-fact version, I would go with this pattern:

DISTINCT (
    UNION (
        SUMMARIZE (
            'Fact_Inventory_Req',
            'Dim_Date'[Date],
            'Dim_SKU'[SKU Key],
            'Dim_Location'[Location Key]
        ),
        SUMMARIZE (
            'Fact_Inventory',
            'Dim_Date'[Date],
            'Dim_SKU'[SKU Key],
            'Dim_Location'[Location Key]
        )
    )
)

It will be easier when SUMMARIZECOLUMNS is (reliably) available for use in measures.

All the best!