Forum Discussion
Distinct Count Based on Multiple Dynamic Column Filters
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.
5 Replies
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?
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
- 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.
Anonymous , Try like can help
sumx(summarize(Table,Table[Customer ID],Table[Channel],"_1",distinctcount(Table[Date])),[_1])
