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: ...
// 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 )
// 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
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?
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
- I wrote the formula based on your not-entirely-clear description. Please play with the formula (changing it) to see whether you can get what you want. I can't do any better since I don't quite understand your requirements. The best way to get your questions answered quickly and correctly is to read this and stick to it: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Slight tweak and this works, for anyone this may help reading in the future:
change VAR __result to this for first date value rather than all date values in the range:
var __result =
SUMX(
VALUES(Customer ID,
var __firstDate =
CALCULATE(
MIN( FT[Metric Date] ),
KEEPFILTERS( FT[Metric] = "visit" ),
ALL( 'Date' )
)Again, thanks for your help Anonymous
- Anonymous
Please mark this topic as resolved so that it helps others. Also, sorry but I don't understand your last post. You say to change var __result but it looks like the code you give is no different than the one I wrote apart from the first argument of SUMX which does exactly the same thing as the first argument in my formula. Could you please paste the whole correct code in here. That will really be helpful. Thanks.