Forum Discussion

Kostas's avatar
Kostas
Icon for Helper IV rankHelper IV
4 years ago
Solved

Equal Values among columns

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...
Need Help
  • Pragati11's avatar
    Pragati11
    4 years ago

    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

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
4 years ago

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's avatar
Kostas
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Kostas
      Icon for Helper IV rankHelper IV
      4 years ago

      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