Distinct Count of Values Based on Two Columns
Question
Tuesday, March 29, 2011 1:19 PM
Hi This is my sample Data set, I need a distinct count of the country and Month Combination by using PowerPivot DAX formulas.
I have attached a sample data set from the Values below if i take unique Country and Month Combination i should get 3. Thanks in advance for the help
| Name | Country | Month |
| Asimov | Russia | Jan |
| HariPrasad | USA | Jan |
| Partha | USA | Jan |
| Cristy | India | Feb |
| John | India | Feb |
All replies (5)
Tuesday, March 29, 2011 4:28 PM âś…Answered | 1 vote
The optimal way is creating a Name_Country calculated column with the expression
= [Name] & [Country]
and then create the Measure:
= COUNTROWS(DISTINCT([Name_Country]))
Marco Russo
http://www.sqlbi.com
http://sqlblog.com/blogs/marco_russo
Marco Russo
Saturday, July 21, 2012 6:29 AM
Having trouble with this one, not working for me.
Seems that the COUNTROWS is expecting a table name and refuses to work with ColumnNames.
How to solve that?
I'm setting all columns up as calculated columns in a PowerPivot.
Sunday, July 22, 2012 6:49 PM
Use =COUNTROWS(DISTINCT(ALL([NameCountry]))))
Thursday, December 22, 2016 12:20 PM
Dear Marco,
I have used this solution, but on a very large fact table ( in my case 1.2 billion rows), this expression generates a very large dataset. I think because the cardinality of Name & country is much higher than the cardinality of name and county alone.
Do you know of a more efficient way of doing this?
I've already experimented with using a rank() function in my source data, but this also generates a very large dataset.
Bas
Thursday, December 22, 2016 1:07 PM | 1 vote
The problem is not how many rows you have, but how many unique values you have in the combined columns.
You can find more details here:
http://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/
Unfortunately, there is no magic: if you need to count the unique combinations of two columns, you have to create this list of unique values: at process time (calculated column) or at query time (combining the two columns).
Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.