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

Measure for "look up"

Hi All, 

 

I'm using PBI live connected to AZURE, so I cannot create columns, only use measures.  

I have multiple tables in the cube, and the two pieces of data I want to referance are in different tables.

 

If table 2 is blank or is the same as the row in table 1 = "YES" 

If table 2 does not match the same row on table 1 = "NO"

 

Table 1 TABLE 2 MEASURE RESULT
Data 1 Data 2 Measure
A   YES
B A NO
C   YES
D B NO
A A YES
B   YES
C A NO
D   YES

 

Thanks in advance, guys!

 

T

2 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

a measure is always to be seen in contect of the "filtering" that its be surrounded. So if you are taking about Tabl1 and Tabl2 in the context of a measure, then these 2 tables have to be in some relation. What I did is to add a index column to your tables and connected them via index. then I used a measure like this

Measure = var Data1m = FIRSTNONBLANK('Data 1'[Data 1];[Data 1])
var Data2m =FIRSTNONBLANK(Data2[Data 2];[Data 2])
Return
if ( (Data1m = Data2m)||Data2m=Blank();"yes";"no")

 

the output usind the index as filter is like this

grafik.png

it's this what you were looking after?


If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

View solution in original post

Hello

 

the solution I presented is no custom column but a measure. So if you are able to create new measure, you can give it a try

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

View solution in original post

6 REPLIES 6
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

a measure is always to be seen in contect of the "filtering" that its be surrounded. So if you are taking about Tabl1 and Tabl2 in the context of a measure, then these 2 tables have to be in some relation. What I did is to add a index column to your tables and connected them via index. then I used a measure like this

Measure = var Data1m = FIRSTNONBLANK('Data 1'[Data 1];[Data 1])
var Data2m =FIRSTNONBLANK(Data2[Data 2];[Data 2])
Return
if ( (Data1m = Data2m)||Data2m=Blank();"yes";"no")

 

the output usind the index as filter is like this

grafik.png

it's this what you were looking after?


If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Anonymous
Not applicable

Hi Jimmy,

Thank you very much for the response and your time.

I am unable to add any additional columns because I’m live connected to Azure.
So from what I I understand, this will not work.

I think I would need to create two variables in the measure with a if statement. I am just not experienced enough in DAX to know exactly how.

Hello

 

the solution I presented is no custom column but a measure. So if you are able to create new measure, you can give it a try

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Anonymous
Not applicable

Hi Jimmy, This works perfect! Thanks, I have also learned a lot. Now I want to work out how to create a measure to count all the "yes" and "no"! 🙂 Cheers

Hello @Anonymous 

 

good luck

 

Jimmy

Anonymous
Not applicable

I hope I have explained this clearly.

Please ask if not.

Regards

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.