cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Evaluate Row Values Using Multiple LookUp Values

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

Table.PNG

 

 

 

 

 

 

Merge Table

I'm tyring to return the values shown in "Merge Table" column "REF DELTA DATA".

Table_Merge.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Evaluate Row Values Using Multiple LookUp Values

Hi @wayers

I use your original table to have a test.

We need two same table:

1.png

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]

 

3.png

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. 

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Evaluate Row Values Using Multiple LookUp Values

Hi @wayers ,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Evaluate Row Values Using Multiple LookUp Values

Hi @wayers 

Your calculate logic is complex, please provide me with more details :

  1. Are Table, ATD Table and WT Table the Original tables in your pbix file.
  2. How can we get REF DELTA DATA column, please show me the calculate logic. And I didnt see this column in your pbix file.
  3. I know Data∆ = [DataB]-[DataA] and Data∆ WT = [DataB WT]-[DataA WT]+0. But in community you said return A-B. Could you tell me that will we use these measure at the time we calculate REF DELTA DATA? If we will, please show the calculate logic.
  4.  Could you show me your solution by merge? Your steps in this solution may help we understand your calculate logic.

 

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. 

Highlighted
Helper IV
Helper IV

Re: Evaluate Row Values Using Multiple LookUp Values

@RicoZhou @AiolosZhao ,

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.

Highlighted
Microsoft
Microsoft

Re: Evaluate Row Values Using Multiple LookUp Values

Hi @wayers

I use your original table to have a test.

We need two same table:

1.png

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]

 

3.png

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. 

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Evaluate Row Values Using Multiple LookUp Values

@RicoZhou ,

Thanks! This is an excellent option for what I was working to achieve.

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors