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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous

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
Anonymous
Not applicable

@v-rzhou-msft @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.

Hi @Anonymous

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. 

Anonymous
Not applicable

@v-rzhou-msft ,

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

v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

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. 

AiolosZhao
Memorable Member
Memorable Member

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





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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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