How to compare four columns, each column in its own table (four tables) using DAX

[mcastro70]

Hi Experts!

I need to create a DAX Calculated Column or Measure on my 5th table comparing the values of four different columns in four different tables. Some columns may and will have blanks, however; if all nonblank values match, the result should still be "True" as seen in row three below or "False" as seen in row four below. Also, all tables have relationships created using a unique ID.

Thanks so much in advance for all the help!

MCastro.

[v-angzheng-msft · Accepted solution]

Hi, @mcastro70 

You could try using this measure and then use conditional formatting to distinguish if the Status is Compliant.

_CountStatus = 
VAR _AM=MAXX(FILTER('Table1','Table1'[Serial Number]=MAX('Table'[Serial Number])),[Am Status])
VAR _ESL=MAXX(FILTER('Table2','Table2'[Serial Number]=MAX('Table'[Serial Number])),[ESL Status])
VAR _DOMS=MAXX(FILTER('Table3','Table3'[Serial Number]=MAX('Table'[Serial Number])),[DOMS Status])
VAR _CMDB=MAXX(FILTER('Table4','Table4'[Serial Number]=MAX('Table'[Serial Number])),[ CMDB+ Status])

VAR _R={_AM,_ESL,_DOMS,_CMDB}
VAR _T=SUMMARIZE(FILTER(_R,[Value]<>BLANK()),[Value])
var _countStatus=COUNTROWS(_T)
RETURN _countStatus

color = IF('M'[_CountStatus]>1,"pink","orange")

Result:

I've tried using the IF function to determine if it's blank, and that doesn't seem to be implemented by the short code. This solution works and is elegant enough for me.

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft · Accepted solution]

Hi, @mcastro70 

You could try using this measure and then use conditional formatting to distinguish if the Status is Compliant.

_CountStatus = 
VAR _AM=MAXX(FILTER('Table1','Table1'[Serial Number]=MAX('Table'[Serial Number])),[Am Status])
VAR _ESL=MAXX(FILTER('Table2','Table2'[Serial Number]=MAX('Table'[Serial Number])),[ESL Status])
VAR _DOMS=MAXX(FILTER('Table3','Table3'[Serial Number]=MAX('Table'[Serial Number])),[DOMS Status])
VAR _CMDB=MAXX(FILTER('Table4','Table4'[Serial Number]=MAX('Table'[Serial Number])),[ CMDB+ Status])

VAR _R={_AM,_ESL,_DOMS,_CMDB}
VAR _T=SUMMARIZE(FILTER(_R,[Value]<>BLANK()),[Value])
var _countStatus=COUNTROWS(_T)
RETURN _countStatus

color = IF('M'[_CountStatus]>1,"pink","orange")

Result:

I've tried using the IF function to determine if it's blank, and that doesn't seem to be implemented by the short code. This solution works and is elegant enough for me.

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mcastro70]

Hi V-angzheng;

Thanks so much for the reply and solution!

It worked perfectly!. This has been a life saver for me. I really appreciate it.

Best Regards!

Mcastro

[mcastro70 · Accepted solution]

Hi v-angzheng-msft

Thanks so much for the reply and solution!!!

It worked perfectly! This has been a life saver for me, I really appreciate the help!

Best Regards!

Mcastro.

[amitchandak]

@mcastro70 , You need to have some ID or some column, basis on that we can can data from these 4 tables into 5th table and compare 

example

New column =

var _1  = maxx(filter(Table1, Table1[ID] = Table5[ID]) , Table1[Value])

var _2  = maxx(filter(Table1, Table2[ID] = Table5[ID]) , Table2[Value])

var _3  = maxx(filter(Table1, Table3[ID] = Table5[ID]) , Table3[Value])

var _4  = maxx(filter(Table1, Table4[ID] = Table5[ID]) , Table4[Value])

return

If(_1 =_2 && _2=_3 && _3 =_4 , true(), false() )

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[mcastro70]

Hi Amitchandak;

First of all I am sorry if I sound as a beginner; that is because I am.

Your solution seems like a good approach; however, it seems that is NOT ignoring the blanks and therefore producing the wrong results. I have included a screenshot of my table testing your solution and you as can see inside the red frame that those should have come back as "Status Compliant" since the AM status and DOMS status are the same, while ESL and CDMB+ statuses are BLANK. Could you please check my DAX and make sure I did not make any mistakes? Also; I wanted to ask you if you by mistake mixed table1 with table2 before the = operator ((Table1, Table2[ID]); and the same for all subsequent tables. Shouldn't they match the table number as (Table2, Table2[ID])?

The data in the green frame is showing the correct result; but that might be coincidential due the BLANK values in the ESL and CMDB columns.

thanks again for you help!

[amitchandak]

@mcastro70 , Either handle blank using isblank(_1) or coalesce

coalesce(_1,_2) = coalesce(_2,_1)

coalesce(_1,"ABC")

You need to handle null in one of the above. Else paste your formula in text I will try

[mcastro70]

Hi again!

Could you be a little more specific with the syntax based on my used case; this is very cryptic for me. Like I said, I am a beginner user.

Do I still use the VAR's and the IF Statement? where and how do I incorporate any of the two options you just suggested ( isblank(_1) / coalesce(_1,_2) = coalesce(_2,_1) )?

Sorry for being a newbie.

Thanks again!