Dynamic grouping based on dynamic max value - possible in Dax?

[Craig_B]

Hi 🙂

I'm trying to build a grouping in dax based on the maximum age of an InstanceID in a user defined time period.
The InstanceID is the unqiue identifier in the data set and is "aged" for every day it is in scope.
The period an InstanceID is in scope could be any number of days >=1.
The measure is a distinct count of InstanceID.
An example grouping would be "count distinct InstanceIDs with maximum age <=3, <=8, >8 in the user defined period.

In the screenshot the closest I can get is the matrix table displaying what I need (2nd visual on the left) but only if I slice on InstanceID .... the 3rd visual on the left is what is displayed without explicitly including InstanceID.

I sort of understand why this is happening - the max value of age has not context to evaluate each individual InstanceID's group before counting the distinct values.... I've just no idea how to do it.

I've tried using switch (see below) and can't get the vMaxAge dynamic bit to work:

Age Bucket:

Max Age in Instance Bucket =
var vMaxAge = CALCULATE(MAX(Disputes[DisputeAgeCalculated])) + 0
RETURN
SWITCH (
TRUE(),
vMaxAge > 0 && vMaxAge <= 3 , "<= 3"
,vMaxAge > 0 && vMaxAge <= 8 , "<= 8"
,vMaxAge > 0 && vMaxAge >8 , "> 8"
)

Count based on Age Bucket:

Distinct Count of Instance ID by Bucket =

var vBucket = SELECTEDVALUE('Age Bucket'[Age Bucket])
var vMaxBucket = [Max Age in Instance Bucket]
var vInstanceCount = CALCULATE(DISTINCTCOUNT(Disputes[InstanceID]))

RETURN

IF (vBucket = vMaxBucket,vInstanceCount)

I need the vMaxAge to calculate for each InstanceID and then count, but this is calculating the max age across all InstanceIDs when InstanceID is not included in the visual.

Age Bucket is a standalone table:

Age Bucket = DATATABLE("Age Bucket", STRING,
{
{"<= 3"},
{"<= 8"},
{"> 8"}

})

I've tried the solution suggested here Use earlier but get an error using Earlier.
Any help very much appreciated!

[amitchandak]

@Craig_B , I think you are looking for

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k