Forum Discussion
Count Distinct Across Multiple Columns in PowerBI
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.
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.
6 Replies
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 attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi,
SUMMARIZE provides a unique table, so there is no need to use DISTINCT .right before.
I want to do a distinct count of all the combinations across these three columnsSUMMARIZE these columns and then COUNTROWS the resulting table variable.
Interesting take on this. I appreciate it.
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.
This worked for me, Thanks.
