Including All Permutations In New Table

[jmsny]

I have a table with about 6 different variables and an identifier variable, along with a bunch of data.  I created a second table using SUMMARIZE with this data but I can't figure out a way to include the permutations that don't have data.

For example, for a certain identifier, there may be:

Parameter 1	Parameter 2	Rows
A	X	400
A	Y	800
B	X	0
B	Y	500

When I create the second table that gets the averages, I only get combinations of AX, AY, and BY for this identifier, but BX is missing.  Then for another identifier, there may be a lack of AX data, so I get AY, BX, and BY.

How can I get these rows that don't have data to show up in my second table?  I've tried a number of other things including using helper tables, but nothing works.

Thanks!

[jmsny]

This is great! I've gotten it working on some of the data.

Now, I have two issues I'm working through.  I have 20+ columns I'm bringing over as averages/counts and applying all of the parameters as filters.  Is there a way to apply the same filter to each of these columns?  When using SUMMARIZE, the filters are auto applied so it was a lot simpler.

The second is that I have a significant amount of measures that already average the data but apply a separate filter.  Is there a way to use measures here?

I am trying to work on a separate way to get these all working through a helper column that combines the data from each of the parameters, but PowerBI is incredibly slow and even creating columns (to get to the point where I can type in the formula, not even to edit it) takes hours, so I just wanted to see if there was a way to get around this.

Thanks!

[v-menakakota]

Hi @jmsny ,
Thank you for reaching out to the Microsoft fabric community forum. 

Glad to hear you’ve got it partly working!
Since you're using a custom table with all combinations, Power BI won’t automatically apply filters like it does with SUMMARIZE. To make this easier, you can define the filter once and reuse it across your columns, instead of writing it out each time.

Yes, you can definitely use the measures you've already created. Even if those measures have their own filters, they’ll still work in your new table. Just make sure they’re picking up the right context from the new combination table.

If this post helps then please mark it as a solution, so that other members find it more quickly. 
Best Regards, 
Menaka.
Community Support Team  

[jmsny]

Hi @v-menakakota.

How can a filter be defined in this situation?  For the example from @DataNinja777, the filter is on the data table and it compares against the individual tables with EARLIER.  If I define the filter in each line, I can use EARLIER, but if I use it in a variable, I get the following error:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

If I use SELECTEDVALUE, I get no DAX error but the column will be blank.  If I use RELATED, I get an error for not having a relationship even though it's not true.  If I leave it blank, as I do in the CALCULATE function on each line, I get an error stating that a single value cannot be determined.

Is a filter variable even possible, or is there another way to define a filter and reuse it?  Thanks!

[v-menakakota]

Hi @jmsny ,

I think you can rewrite your CALCULATE block to use TREATAS, which lets you map values from your combination table (e.g., FinalTable) into filter context for your data table (YourTable), like this:

FinalTable =

ADDCOLUMNS(

    AllCombos,

    "RowCount",

    CALCULATE(

        COUNTROWS(YourTable),

        TREATAS(

            { [Identifier], [Parameter 1], [Parameter 2] },

            YourTable[Identifier],

            YourTable[Parameter 1],

            YourTable[Parameter 2]

        )

    )

)

If this post helps then please mark it as a solution, so that other members find it more quickly. 
Best Regards, 
Menaka.
Community Support Team  

[jmsny]

Hi @v-menakakota,

The issue is I need to reuse the filter across 20+ variables.  To modify the example you gave:

ADDCOLUMNS(

    AllCombos,

"Variable 1",    CALCULATE(COUNTROWS(YourTable),TREATAS(...))

"Variable 2",    CALCULATE(COUNTROWS(YourTable),TREATAS(...))

"Variable 3",    CALCULATE(COUNTROWS(YourTable),TREATAS(...))

...

"Variable 25",    CALCULATE(COUNTROWS(YourTable),TREATAS(...))

)

So instead of putting a TREATAS in each row, I need to create a variable or something else to accomplish the same thing.  If the variable uses TREATAS, it doesn't work.  The idea is to do something like this:

VAR FilterVar = "..."

...

"Variable 1",    CALCULATE(COUNTROWS(YourTable),FilterVar)

Is it possible?

[v-menakakota]

Hi @jmsny ,

In DAX, you can’t store a filter expression like TREATAS(...) in a variable and reuse it inside CALCULATE. CALCULATE expects filter expressions to be written directly (inline), not passed through a variable.
You’ll need to repeat the TREATAS(...) expression in each CALCULATE. It’s a bit repetitive but the only supported method in this case.

If you’re applying the same filter logic across many columns , consider turning your logic into measures instead. You can apply the TREATAS(...) filter inside each measure, and then just reference the measure inside ADDCOLUMNS or visuals.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Best Regards, 
Menaka.
Community Support Team  

[DataNinja777 · Accepted solution]

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,