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.
Hello Community,
Any help is appreciated!!
A am trying to accommodate a solution for evaluating a specific row when looking a values in multiple columns (using a measure).
In the example (PBIX File Here) we have some test data where we are trying to find the difference between the "Run" data and the associated "Ref" data. The "Ref" is referring to a value of a "Run" within the same test.
Our conditions are;
Var A = Find "Data" value by "Run"
Var B = Find "Data" value where "Ref" = "Run" and
"ATD" of "Ref" and "Run" match, and "Test" of "Ref" and "Run" match
Return
A - B
One solution that I have is to merge the table with itself and create the needed relationships through the merge (shown in sample PBIX file), but I was trying to find a solution that does not require transforming the native table, using only a Measure if possible.
Native Table
Merge Table
I'm tyring to return the values shown in "Merge Table" column "REF DELTA DATA".
Solved! Go to Solution.
Hi @Anonymous
I use your original table to have a test.
We need two same table:
We build a calculated column in Table1
Column1 = 'Table1'[Test]&""&'Table1'[Run]&""&'Table1'[ATD]
Build column 2 in Table2
Column 2 = 'Table2'[Test]&""&'Table2'[Ref]&""&'Table2'[ATD]
Then we build a new column to find the New Data in Table1.
New Data = CALCULATE(SUM(Table2[Data]),FILTER(Table2,'Table2'[Column 2]= EARLIER('Table1'[Column1])))
Finally, we get the result.
REF DELTA Data = 'Table1'[Data]-'Table1'[New Data]
You can download the pbix file from this link: Evaluate Row Values Using Multiple LookUp Values
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for offering help with this, here is a latest PBIX file, the original post had out dated data.
This is the query editor for the modifications to the original table in the PBIX file
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5LDsAgCATQu7h2IbS29izG+1+jfoCgsJAYXsJMrQFCXC/xpz8MLR6EPuU+Lp/GuA2NO0VFwSbzHvqEKuqgpKIWyY1PGnALoVfWhgplOZQpaycpD9JSE3IFS/DQ2iHOImo/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t, Run = _t, Data = _t, ATD = _t, #"W ID" = _t, Ref = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", Int64.Type}, {"Run", Int64.Type}, {"Data", Int64.Type}, {"ATD", Int64.Type}, {"W ID", Int64.Type}, {"Ref", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Test", "Ref", "ATD"}, #"Changed Type", {"Test", "Run", "ATD"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Run", "Data"}, {"REF.Run", "REF.Data"}),
#"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Test", Order.Ascending}, {"Run", Order.Ascending}, {"ATD", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "REF DELTA DATA", each [Data]-[REF.Data])
in
#"Added Custom"
This includes how I am getting to the calculated column of "REF DELTA DATA".
I would like to know if I can get to the same solution of the "REF DELTA DATA" column without transforming the table, and just have a DAX measure.
In my data, a "Run" is original testing data gathered, and the "Ref" refers to a "Run" to evaluate another against, where "Ref" value data is = to the same "Run" value data.
The basic math is subtract "Run" 1 from "Run" 2. The "Ref" column is a defined value from which to evaluate vs. allowing a user to select.
Hi @Anonymous
I use your original table to have a test.
We need two same table:
We build a calculated column in Table1
Column1 = 'Table1'[Test]&""&'Table1'[Run]&""&'Table1'[ATD]
Build column 2 in Table2
Column 2 = 'Table2'[Test]&""&'Table2'[Ref]&""&'Table2'[ATD]
Then we build a new column to find the New Data in Table1.
New Data = CALCULATE(SUM(Table2[Data]),FILTER(Table2,'Table2'[Column 2]= EARLIER('Table1'[Column1])))
Finally, we get the result.
REF DELTA Data = 'Table1'[Data]-'Table1'[New Data]
You can download the pbix file from this link: Evaluate Row Values Using Multiple LookUp Values
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks! This is an excellent option for what I was working to achieve.
Hi @Anonymous
Your calculate logic is complex, please provide me with more details :
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
The logic seems complex, could you please give a sample.
e.g. when you select XXX in XXX column, the desired result is XXX, because XXXXXXX.
Thanks.
Aiolos Zhao
Proud to be a Super User!
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 |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |