Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:   Wit...
  • Mariusz's avatar
    Mariusz
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago
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.