Equal Values among columns

[Kostas]

Hello everyone, 

I got a table with risks that looks like the below

Organisation	Business	Customer	InfoSec	Emerging
Organisation 1	Business 1	1		2
Organisation 1	Business 2	2	3	3
Organisation 2	Business 1	3	3	3
Organisation 2	Business 2	1	1	1
Organisation 2	Business 3	2	2

I need to run an analysis in which in a visual or even to the table to check if the value placed across the different risks for each combination of organisation and business is equal. Moreover, I need to not take into account any null values. If only one value applicable in the combination then, just N/A.

The outcome in a table could look like the below:

Organisation	Business	Customer	InfoSec	Emerging	Compare
Organisation 1	Business 1	1		2	FALSE
Organisation 1	Business 2	2	3	3	FALSE
Organisation 2	Business 1	3	3	3	TRUE
Organisation 2	Business 2	1			N/A
Organisation 2	Business 3	2	2		TRUE

Any ideas?

Thanks in advance

[Pragati11]

Hi @Kostas ,

Can you explain the logic on how you derived the Compare column in the second screenshot of your's?

Your explanation is not enough to understand what is the logic here.

Thanks,

Pragati

[Kostas]

Hello and thanks for the reply, 

I am basically comparing the three columns Customer, InfoSec and emerging. 

If the number between the three columns is the same then the last column should return TRUE, if not then FALSE. In case that only one of the three columns has only value then to return N/A. 

Also, the null/blank values should be excluded from the comparison. 

For example:

Emerging = null

Customer = 3

InfoSec = 3 

Compare column = TRUE (as the customer and InfoSec are the same)

Example 2:

Customer = 2

InfoSec = 2

Emerging = 1

Compare column = False.

If all three columns are null then null. 

The comparison should always run between the values that have the same Organisation and Business.

I hope that helps, let me know and I can create a table with more exmaples. 

In excel for example you can write =AND(B2 = B3:B4) but still is considering blank/null values 

[Pragati11 · Accepted solution]

HI @Kostas ,

Based on the expanation you mentioned, I got the following DAX: (I have added explanation at every condition checked in the DAX expression)

Compare = 
var cust = SELECTEDVALUE(orgData[Customer])
var emg = SELECTEDVALUE(orgData[Emerging])
var info = SELECTEDVALUE(orgData[InfoSec])
RETURN
IF(
    cust = emg && cust = info && emg = info, "True", //checkng if all columns have same value
    IF(
        cust = BLANK() && emg = info, "True", // checking if one of columns is Blank and rest of the 2 have same value
        IF(
            emg = BLANK() && cust = info, "True", // checking if one of the 3 columns is Blank and rest of the 2 have same value
            IF(
                info = BLANK() && emg = cust, "True", // checking if one of the 3 columns is Blank and rest of the 2 have same value
                IF(
                    cust = BLANK() && emg = BLANK() && info = BLANK(), "N/A", // checking if all the 3 columns are Blank
                    IF(
                        cust = BLANK() && emg = BLANK(), "N/A", // checking if any of the 2 columns are Blank
                        IF(
                            emg = BLANK() && info = BLANK(), "N/A", // checking if any of the 2 columns are Blank
                            IF(
                                cust = BLANK() && info = BLANK(), "N/A", // checking if any of the 2 columns are Blank
                                "False" // remaining condition gets False which means no value is same in any of the 3 columns
                            )
                        )
                    )
                )
            )
        )
    )
)

When I move it to the table visual:

It seems to align with your explanation. Let me know if this is what you are looking for.

Thanks,

Pragati

[Kostas]

Hi, 

That would work, I was just hoping for a better solution and easier solution. 
In case that I got more than 3 columns then it becomes to complicated. 

Thanks for the answer that would work for now.

Thanks