topic Distinct Count Based on Multiple Dynamic Column Filters in DAX Commands and Tips

Distinct Count Based on Multiple Dynamic Column Filters

Anonymous — Mon, 17 Aug 2020 08:45:49 GMT

I want to create a measure that counts distinct visit days (metric date) per customer that changes based on brand, channel and date filters. This is an example of the table that I have:

With the above in mind, if Brand ID = 2 is filtered on report, it should show 2 distinct visit days.

If Brand = 2 and Channel = 3, it should show 1.

If Customer ID = 1 & 2, it should show 2 visit days.

A distinctcount will obviously exclude the same visit day across customers. I don't know how to amend this to get a count of unique metric dates per customer that then also changes depending on brand and channel filters.

If anyone can help that would be greatly appreciated.

Re: Distinct Count Based on Multiple Dynamic Column Filters

amitchandak — Mon, 17 Aug 2020 08:55:58 GMT

Anonymous , Try like can help

sumx(summarize(Table,Table[Customer ID],Table[Channel],"_1",distinctcount(Table[Date])),[_1])

Re: Distinct Count Based on Multiple Dynamic Column Filters

Mariusz — Mon, 17 Aug 2020 09:51:58 GMT

Hi Anonymous

Try this Measure.

Measure = COUNTROWS( SUMMARIZE( 'Table', 'Table'[Customer ID], 'Table'[metric date] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Distinct Count Based on Multiple Dynamic Column Filters

Anonymous — Mon, 17 Aug 2020 13:36:10 GMT

Mariuszthis works when filtering on brand and channel but across the entire table, unfiltered, I get too many records. It looks like some dates are being double counted.

Any thoughts?

Re: Distinct Count Based on Multiple Dynamic Column Filters

Anonymous — Mon, 17 Aug 2020 16:49:10 GMT

Here is an example of some of the data from one customer:

Using COUNTROWS(SUMMARIZE('Table', customer_id, metric_date)) works when filtering by brand or channel, however, unfiltered this shows the number of visit days as 5. However, this should show as 4 since there are only 4 unique metric dates.

Calling Anonymous for a solution on this one (if that's acceptable). Please help

Re: Distinct Count Based on Multiple Dynamic Column Filters

Anonymous — Mon, 17 Aug 2020 19:47:24 GMT

Hi there.

The formula COUNTROWS(SUMMARIZE('Table', customer_id, metric_date)) is correct and should show 4 for an unfiltered table. If you get 5, please create a calculated table with the formula SUMMARIZE('Table', customer_id, metric_date) and see what it returns. It should return 4 rows. If it returns 5, as you claim, something's wrong with your data types.