Forum Discussion
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.
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
8 Replies
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.
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?
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
