cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wayers Member
Member

DAX Help - CrossFilter and LookUp

Community,

I am trying to return a difference using multiple tables. I need assistance with DAX for a measure that will return the correct result.

I have two tables...

 

Table1 where;

Test Index Number = Populated as a sequential index for that days testing

SeqID = Set of requirements for a test

Run ID = Unique ID for a grouping of SeqID and a difference in the tested parts

Ref ID = A previous or upcoming Run ID to compare data between

 

Table2 where;

Test Index Number = Populated as a sequential index for that days testing (Matches table1)

Data = Data collect

 

Tables and Relationships (tables connected by Test Index Number)

 

diff3.JPGdiff4.JPG

 

 

 

 

 

 

 

 

 

In the image below the green column would be what is looked up and the yellow column would be the difference between the two data. The yellow column is what I'm trying to replicate.

 

diff2.JPG

 

A test PBIX file can be found here.

 

6 REPLIES 6
kexley Regular Visitor
Regular Visitor

Re: DAX Help - CrossFilter and LookUp

As long as the data in the green column is connected via a relationship to Table1/Table2, then the following code should work in a calculated column:

Difference =
Table2[Data] - RELATED ( RefData[Ref Data] )

This uses the RELATED function to get the relevant value from the green column.

wayers Member
Member

Re: DAX Help - CrossFilter and LookUp

@kexley ,

I do not have the ref data as a column and am trying to create a measure for it. Any thoughts on how to replicate the ref data with a measure?

 

kexley Regular Visitor
Regular Visitor

Re: DAX Help - CrossFilter and LookUp

Is the ref data in another table?

Could you connect the tables by the Test Index Number?

wayers Member
Member

Re: DAX Help - CrossFilter and LookUp

I need to pull the ref data from the data column in the  by looking at the Ref ID.

kexley Regular Visitor
Regular Visitor

Re: DAX Help - CrossFilter and LookUp

So is the ref data calculated? If so, how would you want it to be calculated?

In the example file you posted there's no field called ref data.

wayers Member
Member

Re: DAX Help - CrossFilter and LookUp

@kexley 

The ref data would come from the data column.

The Ref ID is used to establish a comparison. 

Maybe we could think of it as we would like to compare Test Index Number 1,2 and 3, vs. Test Index Number 7,8,9

The Run ID is a way to group the Test Index Numbers.

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors