Forum Discussion
Distinct count on dynamic MIN(datekey)
- 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 )
// 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 )
- Anonymous6 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?
- Anonymous6 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
- Anonymous6 years agoNot applicableI 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
- Anonymous6 years agoNot applicable
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