Forum Discussion
Distinct Row Count
Hi All,
My requirement is to count distinct rows.
Sample Data:
| Area | Country | Brand |
| A1 | c1 | b1 |
| A1 | c1 | b1 |
| A1 | c2 | b1 |
| A1 | c2 | b3 |
| A1 | c2 | b3 |
| A1 | c4 | b2 |
| A1 | c3 | b1 |
| A1 | c4 | b5 |
| A1 | c4 | b1 |
| A1 | c5 | b1 |
| A1 | c5 | b2 |
| A2 | c6 | b1 |
| A2 | c7 | b2 |
| A2 | c8 | b3 |
| A2 | c9 | b4 |
| A2 | c10 | b5 |
| A2 | c11 | b6 |
| A2 | c12 | b7 |
when I filter A1 in Area and c1,c2 in-country means the brand row count value show as 4. It should take distinct count for c1(b1,b2) as 2 and c2 for 2(b3,b1) so total as 4.
| Area | Country | Brand |
| A1 | c1 | b1 |
| A1 | c1 | b1 |
| A1 | c2 | b1 |
| A1 | c2 | b3 |
| A1 | c2 | b3 |
| A1 | c1 | b2 |
While I am using a distinct Dax measure it takes a total distinct count but wants distinct value based on country.
Please help me to identify the solution. Thanks in Advance.
Hi Shreeram04 ,
Here are the steps you can follow：
1. Create measure.
Measure = var _selectarea=SELECTEDVALUE('Table'[Area]) var _selectcountry=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Area]=_selectarea),"1",[Country]) return CALCULATE(DISTINCTCOUNT('Table'[Brand]),FILTER(ALL('Table'),'Table'[Area]=_selectarea&&'Table'[Country] in _selectcountry))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi Shreeram04 ,
Here are the steps you can follow：
1. Create measure.
Measure = var _selectarea=SELECTEDVALUE('Table'[Area]) var _selectcountry=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Area]=_selectarea),"1",[Country]) return CALCULATE(DISTINCTCOUNT('Table'[Brand]),FILTER(ALL('Table'),'Table'[Area]=_selectarea&&'Table'[Country] in _selectcountry))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
"My requirement is to count distinct rows"
I don't think that's what your requirement is. It sounds more like a "filtering up" pattern where you want to identify other rows outside of the filter context as well. The distinct count for your given example is indeed 3. But as I understand your expected result is 4 because you want to look beyond the combined filter, right?
Filters in DAX are applied together.
Area = A1 and Country in ( c1, c2 )
rather than
Area = A1 or Country in ( c1, c2 )
