Power BI DAX measure calculation

Question

Step1:

I have created a calculated table containing Level, Location, L2code from level sales data. I need to create a report that will count rows based on the level1 group. Note that there are more levels in the table. This is only an example of one of the levels.

How can I count the rows for each level1?

Step2:

I need to create all combinations of counts based on location and l2 code and count the numbers. like in example 2 location and 8 distinct l2code so it should be 2*8 =16 total possible rows.

How can I achieve this using the DAX measure?

Source data file

Output report

mkRabbani mkRabbani · Accepted Answer · 2021-08-02T04:20:12

first one is a simple measure as below-

DistinctRowCount = Count(your_table_name[Level1])

Second one is bit tricky and you can try this below measure-

CrossCount = 

var distinct_location = 
calculate(
    distinctcount(your_table_name[Location]),
    allexcept(
        your_table_name, 
        your_table_name[Level1], 
        your_table_name[Location]
    )
)

var distinct_l2code = 
calculate(
    distinctcount(your_table_name[l2code]),
    allexcept(
        your_table_name, 
        your_table_name[Level1], 
        your_table_name[Location],
        your_table_name[l2code]
    )
)

return distinct_location * distinct_l2code

Sample output-

Power BI DAX measure calculation

1 Answers