Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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"
Solved! Go to 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
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...
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |