Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Thank you very much! This worked like a charm 😄
Thanks a lot for your detailed explanation! I was able to complete all steps and things worked as intended!
Thanks a lot!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |