Compare two columns

[Anonymous]

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.  

[Shishir22]

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!

[Anonymous]

@Shishir22 

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.

[PaulDBrown · Accepted solution]

@Anonymous 

Try: 

Comparison measure =
SUMX (
    SUMMARIZE (
        Facttable,
        Table2[columnA],
        Table1[columnD],
        "@result", IF ( SUM ( Table2[columnA] ) = SUM ( Table1[columndD] ), 0, -1 )
    ),
    [@result]
)

[PaulDBrown]

@Anonymous 

Any chance you can provide a dummy dataset or PBIX file?

[Anonymous]

Unfortunately I can't but I think the question is quite clear to answer 🙂

[PaulDBrown]

@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