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.
I need to compare two columns from two different tables which there is no relation between them but they both are connected to a fact table.
Table2------Key1-------- Facttable
Facttable---Key2-------- Table1
The fact table has two keys; key1 and key2.
I want to compare Table2_columnA with Table1_columnD, if they have the same value then return 0 otherwise return -1.
I tried with related funcation but getting error.
Solved! Go to Solution.
@Anonymous
Try:
Comparison measure =
SUMX (
SUMMARIZE (
Facttable,
Table2[columnA],
Table1[columnD],
"@result", IF ( SUM ( Table2[columnA] ) = SUM ( Table1[columndD] ), 0, -1 )
),
[@result]
)
Proud to be a Super User!
Paul on Linkedin.
Hi @Anonymous ,
Can you please elaborate your requirement a bit?
How is final result expected and against what it needs to be displayed?
Do you need to result in table visual?
Thanks!
I try to explain more with an example:
I want to compare these two columns in a measure and if both has the same value measure returns 0 otherwise return -1; Then I want to use the returned value in report as 'Based on field' for 'Conditional formating' to use color in report.
For example for rows 1,3,5,6 the returned value would be 0 and for other rows would return -1.
@Anonymous
Try:
Comparison measure =
SUMX (
SUMMARIZE (
Facttable,
Table2[columnA],
Table1[columnD],
"@result", IF ( SUM ( Table2[columnA] ) = SUM ( Table1[columndD] ), 0, -1 )
),
[@result]
)
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Any chance you can provide a dummy dataset or PBIX file?
Proud to be a Super User!
Paul on Linkedin.
Unfortunately I can't but I think the question is quite clear to answer 🙂
@Anonymous
The question might seem clear to you, but we need more information. What data type are the columns you want to compare? what are the relationships between the tables? how are you hoping to display the result?...
Please read the following recommendations
How to get your question answered quickly
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |