IF condition based on two different tables

[Anonymous]

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.

[Jorgast]

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]

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:

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

[Thejeswar · 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:

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

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

[HDerlagen]

Thank you very much! This worked like a charm 😄

[Anonymous]

@Thejeswar

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

Thanks a lot!

[Anonymous]

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

[Thejeswar]

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

[poweruser999]

Are you creating a column or a calculated  measure.

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