cancel
Showing results for 
Search instead for 
Did you mean: 
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

7 REPLIES 7
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

View solution in original post

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

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 @Monikar26 ,

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors