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

IF condition based on two different tables

Hi guys,

 

 

I need to compare two values from two different tables, and I believe perhaps a calculated measure could solve this. But I haven't figured out a way to actually do this.

 

I have two different tables, "Enhancements" and "Budget", and I need to compare the value from the column "May" in both of them. If the value is 0 or null in both of them, I need to create a new column and mark those with an X, so I can hide these.

 

Can anyone help me?  thanks a lot.

1 ACCEPTED SOLUTION

Hi @Anonymous,

For using Related(), you need to have a direct active relationship between the tables. In your case, Budget and Enhacements.

 

If possible, create a link between them. In case if not possible, try to create an inactive relationship between them and use the USERELATIONSHIP() DAX in your calculated column to get the desired column from Enhacements table. But I am not sure, if it will allow you to use something like RELATED() with it. Need to check on this functionality. Instead you can use SUMMARIZE() Function for the same

 

Model:userelationship_relation.PNG

 

 

1. Create the column as below. I was able to achieve the first column using a different approach as shown below

 

 

Enhancements.Value = CALCULATETABLE(SUMMARIZE(Enhancements,Enhancements[VALUE]), USERELATIONSHIP(Budgets[ID],Enhancements[ID]))

This shows my Budget table before and after including that new column. Also shows the other tables that I have considered

 

userelationship_table.PNG

 

 

The Other steps would remain the same

 

2. Column2 = If('Budget'[Value] = 0 || 'Budget'[Value] = "" || 'Enhancement'[Value] = 0 || 'Enhancement'[Value] = "",X, [Value] 

3. Then in your report you can filter out X for column 2

 

P.S. Get Detailed info of those functions and their usage from the links provided

 

Regards,

Thejeswar

View solution in original post

8 REPLIES 8
Jorgast
Resolver II
Resolver II

In the budget table

1. Column = Related('Enhancement'[Value])

2. Column2 = If('Budget'[Value] = 0 || 'Budget'[Value] = "" || 'Enhancement'[Value] = 0 || 'Enhancement'[Value] = "",X, [Value] 

3. Then in your report you can filter out X for column 2

 

I am sure there is an easier way to do this somehow, but i like to be able to step through the process.

Anonymous
Not applicable

Hi @Jorgast

 

Thanks a lot for your help. But I can't seem to be able to even create the first column. When I try to, it says there's no such table or it has no established relationship with the other table, eventhough they are connected through a fact table:

1131312312313.PNG

 

Any ideas on what could be triggeting this, or maybe if there's a different way of achieving the same result?

Hi @Anonymous,

For using Related(), you need to have a direct active relationship between the tables. In your case, Budget and Enhacements.

 

If possible, create a link between them. In case if not possible, try to create an inactive relationship between them and use the USERELATIONSHIP() DAX in your calculated column to get the desired column from Enhacements table. But I am not sure, if it will allow you to use something like RELATED() with it. Need to check on this functionality. Instead you can use SUMMARIZE() Function for the same

 

Model:userelationship_relation.PNG

 

 

1. Create the column as below. I was able to achieve the first column using a different approach as shown below

 

 

Enhancements.Value = CALCULATETABLE(SUMMARIZE(Enhancements,Enhancements[VALUE]), USERELATIONSHIP(Budgets[ID],Enhancements[ID]))

This shows my Budget table before and after including that new column. Also shows the other tables that I have considered

 

userelationship_table.PNG

 

 

The Other steps would remain the same

 

2. Column2 = If('Budget'[Value] = 0 || 'Budget'[Value] = "" || 'Enhancement'[Value] = 0 || 'Enhancement'[Value] = "",X, [Value] 

3. Then in your report you can filter out X for column 2

 

P.S. Get Detailed info of those functions and their usage from the links provided

 

Regards,

Thejeswar

Thank you very much! This worked like a charm 😄

Anonymous
Not applicable

@Thejeswar

 

Thanks a lot for your detailed explanation! I was able to complete all steps and things worked as intended!

 

Thanks a lot! Smiley Very Happy

Anonymous
Not applicable

if [Custom ID] = Table.Column ([HistoricalformsaData], "Custom ID") then "Duplicate" else "New"

 

Created a custom column. But did not get the duplicate value. Need some help

Hi @Anonymous ,

There could be multiple reasons for this. Can explain on this only if we know what is trying to be achieved here using this if clause. and some more background on your need

Are you creating a column or a calculated  measure.

 

If your data is not sensitive, please post ur pbix here.

 

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.