Forum Discussion
Distinct count on dynamic MIN(datekey)
New with DAX so bear with me.
I am trying to derive a count of customers with a first visit, filtered by brand, or channel, or date, or a combination of all.
Here is an example data set:
I only want to count unique customers where they have had a first visit, dependent on filter context. A first visit is MIN(Metric Date).
I have tried this formula:
CALCULATE(DISTINCTCOUNT(Customer ID), Metric = "Visit",
FILTER('Table', Metric Date<=MAX('Dim Date'[dim date key])&&Metric Date>=MIN('Dim Date'[dim date key]))
)
The problem with this is that if I filter on all brands and a metric date >=27/08/2019, this measure puts the first visit date as 27/08/2019. However, this is only accurate if there is a filter for brand = 1. If looking at all brands, the first visit date is actually 26/08/2019 for customer 1, and therefore they shouldn't be counted.
I hope that makes sense, i.e. the first visit date isn't fixed. Across all brands it is the MIN(metric date) regardless of date filter selected. For a single brand or channel selection, it is the MIN(metric date) for that particular brand or channel.
If anyone can help, I would be very grateful.
// Let's say that the table you're showing // is the fact table, FT, and it's connected // on the obivous fields to dimensions: // Customer, Brand, Channel, Date. // The other fileds, Metric and Venue, // should also have their own dimensions // but I'll use Metric as it is. Please // note as well, that you should never // drop a column from a fact table onto // the canvas if there is a dimension // attached to it. This will almost always // mess up calculations. Such columns should // always be hidden. // Please come up with a better name // for the measure. The name of the // measure should give a hint at what // it really does. Thanks. [# Cust With First Visit] = var __startDate = MIN( 'Date'[Date] ) var __endDate = MAX( 'Date'[Date] ) var __result = SUMX( Customer, var __firstDate = CALCULATE( MIN( FT[Metric Date] ), // If you don't want to intersect // the filter on Metric with "visit", // just remove the keepfilters wrapping // and only leave the condition under it. KEEPFILTERS( FT[Metric] = "visit" ), ALL( 'Date' ) ) return (__firstDate >= __startDate) * (__firstDate <= __endDate) ) return if( __result, __result )
12 Replies
- Greg_Deckler
Community Champion
Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.In your case you would be using COUNTX. The SUMMARIZE should ensure that things are distinct if you group by customer.
// Let's say that the table you're showing // is the fact table, FT, and it's connected // on the obivous fields to dimensions: // Customer, Brand, Channel, Date. // The other fileds, Metric and Venue, // should also have their own dimensions // but I'll use Metric as it is. Please // note as well, that you should never // drop a column from a fact table onto // the canvas if there is a dimension // attached to it. This will almost always // mess up calculations. Such columns should // always be hidden. // Please come up with a better name // for the measure. The name of the // measure should give a hint at what // it really does. Thanks. [# Cust With First Visit] = var __startDate = MIN( 'Date'[Date] ) var __endDate = MAX( 'Date'[Date] ) var __result = SUMX( Customer, var __firstDate = CALCULATE( MIN( FT[Metric Date] ), // If you don't want to intersect // the filter on Metric with "visit", // just remove the keepfilters wrapping // and only leave the condition under it. KEEPFILTERS( FT[Metric] = "visit" ), ALL( 'Date' ) ) return (__firstDate >= __startDate) * (__firstDate <= __endDate) ) return if( __result, __result )
