Forum Discussion
Anonymous
6 years agoNot applicable
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: ...
- Anonymous6 years ago
// 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 )
Anonymous
6 years agoNot applicable
Anonymous
Thank you for the repsonse.
This seems to count all visits in the specified range, rather than the number of first visits only.
Any thoughts?
Anonymous
6 years agoNot applicable
Also, your assumptions are correct, re: applicable dimensions on the relvant columns and being used instead of the fact table columns. For the sake of ease I left them out of the post