Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Craig_B
Regular Visitor

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

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.


Screenshot 2023-08-17 080852.png

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!

1 REPLY 1
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.