Forum Discussion
Help with Distinct Count with Filters
I'm having some difficulties figuring out the correct logic/syntax in DAX that will get me a distinct count of locations whose sales, when summed do not equal 0.
Below is a table showing the data (not all). You'll notice location 170 is basically a net sum of zero. I do not want to include those in my distinct count.
Here is another view of the data, execpt I swapped out the sales field with a "measure" TY Sales summing the sales.
The distinct location count is 12, but what I really want is the count to equal 10 by not counting location 170 and 199.
I've done this DISTINCTCOUNT(TableName[Location]), but of course that does not filter out the location with sales summed up to 0.
And I've seemed to have tried every FILTER, SUMMERIZE option and still can't make it work. I'm sure I'm missing something simple but just can't figure it out.
Any help pushing me in the right direction would be greatly appreciated.
This may not be the best, but try this
Measure =
COUNTAX(
SUMMARIZE(
'Table',
'Table'[Location],
"X", IF(SUM('Table'[TY Sales]) = 0, BLANK(),1)
),
[X]
)Hi Intl_PBI ,
You can achieve your goal using the following measure:
countDistinct = COUNTROWS( FILTER( VALUES( TableName[Location] ), [TY Sales] <> 0 ) )Your output will look like this, notice that in table is 5 locations, one containing 0 TY Sales, so its excluded in the count measure, thats why the return is zero
6 Replies
Hi Intl_PBI ,
You can achieve your goal using the following measure:
countDistinct = COUNTROWS( FILTER( VALUES( TableName[Location] ), [TY Sales] <> 0 ) )Your output will look like this, notice that in table is 5 locations, one containing 0 TY Sales, so its excluded in the count measure, thats why the return is zero
Thank you for your quick response and help. This worked perfectly.
This may not be the best, but try this
Measure =
COUNTAX(
SUMMARIZE(
'Table',
'Table'[Location],
"X", IF(SUM('Table'[TY Sales]) = 0, BLANK(),1)
),
[X]
)Hi Tuta_in_YYC,
Just letting you know that your solution did work. Thanks!
Thanks! The other solutions are more elegant and performant. Awesome that I learned something new from this too.
Hi,
Drag this measure to a card visual
Measure = countrows(filter(values(Data[Location]),[ty sales]>0))
Hope this helps.
