Including All Permutations In New Table

Hi jmsny ,

 

The issue you're facing is that SUMMARIZE only returns existing combinations in the data, so any combination that has no rows (like B-X) is excluded. To include all possible permutations—even those without data—you need to generate them explicitly using a cross join of the distinct values. First, create separate helper tables using DISTINCT for each of the variables you're trying to combine:

DimParameter1 = DISTINCT(YourTable[Parameter 1])
DimParameter2 = DISTINCT(YourTable[Parameter 2])
DimIdentifier = DISTINCT(YourTable[Identifier])

Then, use CROSSJOIN to generate every possible combination of Identifier, Parameter 1, and Parameter 2:

AllCombos =
CROSSJOIN(
    DimIdentifier,
    DimParameter1,
    DimParameter2
)

Finally, use ADDCOLUMNS along with a CALCULATE and FILTER to bring in the actual data (such as row count or average) for each combination:

FinalTable =
ADDCOLUMNS(
    AllCombos,
    "RowCount",
    CALCULATE(
        COUNTROWS(YourTable),
        FILTER(
            YourTable,
            YourTable[Identifier] = EARLIER(DimIdentifier[Identifier]) &&
            YourTable[Parameter 1] = EARLIER(DimParameter1[Parameter 1]) &&
            YourTable[Parameter 2] = EARLIER(DimParameter2[Parameter 2])
        )
    )
)

This will return all permutations regardless of whether the combination exists in your fact table, and it will return blank or zero values for combinations with no matching rows.

 

Best regards,