Count Distinct Across Multiple Columns in PowerBI

[sajtx725]

Hi,

I'm trying to do a distinct count across multiple rows in Power Bi.  But I noticed that the DISTINCTCOUNT() function in power bi only takes one column. 

For example, let's say I have multiple columns in my table for product, category, and subcategory.  I want to do a distinct count of all the combinations across these three columns.  How can I do that? 

I appreciate any support.  Thanks.

[v-pnaroju-msft · Accepted solution]

Thank you, @Ritaf1983 , @lmolus , and @lbendlin , for your responses.

Hi sajtx725,

We sincerely appreciate your inquiry through the Microsoft Fabric Community Forum.

In addition to the responses provided by @Ritaf1983 , @lbendlin , and @lmolus , kindly find attached a sample PBIX file and a screenshot for your reference. To obtain the distinct count of combinations across multiple columns such as Product, Category, and Subcategory, we have created a calculated column by concatenating these fields using the "&" operator, and then applied a DISTINCTCOUNT measure on that column.

We hope that the information provided will assist in resolving your issue. Should you have any further questions or require additional assistance, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[sajtx725]

This worked for me, Thanks.

[lbendlin]

 I want to do a distinct count of all the combinations across these three columns

SUMMARIZE these columns and then COUNTROWS the resulting table variable.

[sajtx725]

Interesting take on this.  I appreciate it.

[Ritaf1983]

Hi @sajtx725 
If you need to count unique values across 3 columns :

You need to union the columns .
It can be done virtually inside DAX calculation 
Like :

DistinctValuesAcross3Columns =

COUNTROWS (

    DISTINCT (

        UNION (

            SELECTCOLUMNS('Table', "Value", 'Table'[Column 1]),

            SELECTCOLUMNS('Table', "Value", 'Table'[Column 2]),

            SELECTCOLUMNS('Table', "Value", 'Table'[column 3])

        )

    )

)

If the goal is to count unique combibnations than you can use Summirize :

DistinctValuesCombinations =

COUNTROWS (

    SUMMARIZE('Table','Table'[Column 1],'Table'[Column 2],'Table'[Column 3])

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[lmolus]

Hi,

SUMMARIZE provides a unique table, so there is no need to use DISTINCT .right before.