Forum Discussion

Kostas's avatar
Kostas
Helper IV
4 years ago
Solved

Equal Values among columns

Hello everyone, 

 

I got a table with risks that looks like the below

OrganisationBusinessCustomerInfoSecEmerging
Organisation 1Business 11 2
Organisation 1Business 2233
Organisation 2Business 1333
Organisation 2Business 2111
Organisation 2Business 322 

 

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:

OrganisationBusinessCustomerInfoSecEmergingCompare
Organisation 1Business 11 2FALSE
Organisation 1Business 2233FALSE
Organisation 2Business 1333TRUE
Organisation 2Business 21  N/A
Organisation 2Business 322 TRUE

 

Any ideas?

 

Thanks in advance

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

4 Replies

  • Pragati11's avatar
    Pragati11
    Super 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
      Helper 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
        Super 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