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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Change calculation rules based on slicer

I know this is probalby the most "beaten to death" request in this forum, but I can't seem to solve it. I tried using HASONEFILTER, and HASONEVALUE solutions, which works fine when it5's two table configuration. Mine has 3 and I can't make it work.

The situation: I need to calculate a Risk column (Normal, Overdue) in the table of units, based on the unit's age. The Overdue threshold are different for different statuses and are kept in a separate table. The simple DAX column works fine and dandy until I want to introduce another set of thresholds for a scenario 2, and want to be able to recalculate Risk on the basis of Scenario slider. Please help.

units_screen.pngunits_DM.png

 

Current formula for Risk column is:

Risk = IF(units[age]>= LOOKUPVALUE(rules[over],rules[status], units[status]), "3. Over", "1. Normal")

 

 

Naturally the formual breaks when Scenario 2 is introduced. I tried to use different combinations of relationships, and HASONEFILTER measure solution, but cannot find the right combination of measures and columns.

 

Thanks!

 

P.S.: Couldn't figure out how to attach PBIX, so:

Units table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc6xDcAgDETRXVxTYBscmCMlYv81CBIF+k0apHvibI8hr0qSsO/RLDMd6AVQHGD6B3rP2AuqA3oFmAGCP9q9xTnDWdlQCQ9yIRiyOic0NjqgsRI83DLvRPY4MBc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [status = _t, age = _t, units = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"status", type text}, {"age", Int64.Type}, {"units", Int64.Type}})
in
    #"Changed Type"

 

Rules table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk5OzUssysw3VNJRCgYRhgZAwthAKVYHTdIISBiBJM2wSBpDNOkomaJKGsGNNQWpMMWUBBsLkjTBImkM0QQ0FigZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [scenario = _t, status = _t, risk = _t, over = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"status", type text}, {"risk", Int64.Type}, {"over", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([scenario] = "Scenario1"))
in
    #"Filtered Rows"

 

Scenario table

 

let
    Source = rules,
    #"Removed Duplicates" = Table.Distinct(Source, {"scenario"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"scenario"})
in
    #"Removed Other Columns"

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Please find a file with a solution attached. I've created the Units table in DAX but you should do it in Power Query (which could be even simpler). Please investigate the solution thoroughly.

 

Best

D

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Please find a file with a solution attached. I've created the Units table in DAX but you should do it in Power Query (which could be even simpler). Please investigate the solution thoroughly.

 

Best

D

Anonymous
Not applicable

Fantastic! I was almost there but I did not know about GENERATE function! In databas terminology your script creates a VIEW based on multiople TABLES... Brilliant! This is the technique I was looking for! Can't thank you enough, friend.

 

P.S.: Now I'll see how this works on my bigger tables...

Anonymous
Not applicable

Good it works for you. However, for really big tables you should use Power Query to do what I did in DAX. That's because Power Query has been designed to work as a mashup engine. DAX has a different purpose in life.

Best
D
Anonymous
Not applicable

Agree. My large table isn't that large ~50K records, but for my specific task I modified your DAX to SUMMARIZE atomic data into a much smaller aggregate before GENERATEing the Cartesian product, and everything works great. See below is the life implementation of your technique. The colors are "On time", "At Risk", and "Overdue". Thanks again!

 

scenario.gif

Anonymous
Not applicable

OK, now you'll help me. Tell me the name of the software that generates these live pics...

Thanks.

Best
D
Anonymous
Not applicable

I used SnagIt, it generally does PrtScr well, but also allows to capture the video. It's expensive, so of all free options I will recommend OBS Studio - good for capturing screencams and playthroughts 😉 After you capture this in the form of video file, run it through EZGif Maker to make a postable GIF.

Anonymous
Not applicable

Thanks.

Best
D

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.