Forum Discussion

awsiya's avatar
awsiya
Icon for Helper I rankHelper I
2 years ago
Solved

Both Column Values Match including a blank one

Hello all,

I am encountering an issue when using DAX to perform the following calculation. I have a simple table containing only two columns, "Sorted" and "Preferred." The task at hand is to determine the count of values in the "Sorted" column that match exactly those in the "Preferred" column. Additionally, if a value in the "Sorted" column corresponds to a BLANK cell in the "Preferred" column, it should also be considered a match. However, when both cells are either "blank or Null," they should not be counted. In the given example, the DAX calculation should yield "4" for the variable "SortedMatchesPreferred," with the breakdown as follows:

The row with '2A' matches '2A'.
The last row with '3' matches '3'.
The fourth row with '5' has a BLANK value in the corresponding row (which should be counted as a match).
The seventh row with '4' has a BLANK value in the corresponding row (which should also be counted as a match).

 

 





Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    awsiya if you don't want to add a column you can also a measure directly:

     

    Sorted Matched Preferred Measure = 
COUNTROWS ( FILTER ( Table, COALESCE ( Table[Preferred], Table[Sorted] ) = Table[Sorted] ) )

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    awsiya it will be easier if you add a column that compares the values and then simply sum on this new column:

     

    Compare Column = 
INT ( COALESCE ( Table[Preferred], Table[Sorted] ), Table[Sorted] )

Sorted Matched Preferred Measure = SUM ( Table[Compare Column] )
    • awsiya's avatar
      awsiya
      Icon for Helper I rankHelper I
      2 years ago

      Thanks Parry, I am getting the result as "5" and not '4', i guess my one is counting the Blank cells (which is row 5 as both are blank) and as such giving 5. I wanted to get the result as just "4". Is there a way to sort it please?

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    awsiya if you don't want to add a column you can also a measure directly:

     

    Sorted Matched Preferred Measure = 
COUNTROWS ( FILTER ( Table, COALESCE ( Table[Preferred], Table[Sorted] ) = Table[Sorted] ) )
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    awsiya change the measure to this:

     

    Sorted Matched Preferred Measure = 
COUNTROWS ( 
   FILTER ( 
      Table, 
      COALESCE ( Table[Preferred], Table[Sorted] ) = Table[Sorted] && 
      NOT ISBLANK ( Table[Sorted] ) 
   )
)