Distinct Count Based on Multiple Dynamic Column Filters

[Anonymous]

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.

[Mariusz · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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]

@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?

[amitchandak]

@Anonymous , Try like can help

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