Change calculation rules based on slicer

[Anonymous]

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.

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"

[Anonymous · Accepted solution]

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]

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]

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]

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!

[Anonymous]

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

Thanks.

Best
D

[Anonymous]

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]

Thanks.

Best
D