Distinct Row Count

[Shreeram04]

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.

[Anonymous · Accepted solution]

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

[lbendlin]

"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 )