Forum Discussion
Compare two columns
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.
Anonymous
Try:
Comparison measure = SUMX ( SUMMARIZE ( Facttable, Table2[columnA], Table1[columnD], "@result", IF ( SUM ( Table2[columnA] ) = SUM ( Table1[columndD] ), 0, -1 ) ), [@result] )
6 Replies
Anonymous
Any chance you can provide a dummy dataset or PBIX file?
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
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] )
