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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
DSQU
Frequent Visitor

Row Groupings for Measure Not Showing As Expected

I am trying to group the results of my measure into buckets with a dax SWITCH statement, but i can't seem to get it to work. 

I would expect it to put the Daniel account into bucket 1 and the Robert account into bucket 2

 

DSQU_0-1681841361799.png

 

 

I've had to slowly build my measure to work so this is the layers, starting at the begining: 

I have data that looks like this: 

DSQU_2-1681841113179.png

 

 

 

My first measure looks like this: 

 

 

 

PrincipalBalDailyFirst = 
CALCULATE ( MIN ( AccountsHistory[Balance Daily] ), FIRSTDATE ( AccountsHistory[Date] ) )

 

 

 

Second measure

 

 

 

PrincipalBalDailyLast = 
CALCULATE ( MIN ( AccountsHistory[Balance Daily] ), LASTDATE ( AccountsHistory[Date] ) )

 

 

 

 

Difference: 

 

 

 

PrincipalBalLastVsFirst = ([PrincipalBalDailyLast]) - [PrincipalBalDailyFirst]

 

 

 

 

This simply calcualtes for each account, depending on the date range slicer selection, the first balance, the last balance, and the change in balance.

 

I had to use this measure to get my totals in the pivot table to work, which finally seem to:

 

 

 

Balance Change = 
SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] )

 

 

Test measure: 

Test = SWITCH (
    TRUE (),
     SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] ) < 0, "1",
     SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] )>= 0 && SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] ) <= 260000, "2",
    "3"
)

 

Now I just want to bucket the results for the Balance Change measure for each account, into buckets. For example, if the change was more than 1 million, call it "greater than 1 milllion balance change" and it can drill down all the accounts in the matrix. 

 

Any help would be appreciated, thanks! 

 

Dave 

0 REPLIES 0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.