The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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:
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
User | Count |
---|---|
12 | |
9 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
14 | |
9 | |
7 |