Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
awsiya
Frequent Visitor

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).

 

awsiya_0-1698692959009.png

 





1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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] ) )


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@awsiya change the measure to this:

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you, it worked 🙂

parry2k
Super User
Super User

@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] ) )


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

tackytechtom
Super User
Super User

Hi @awsiya ,

 

How about this:

tackytechtom_0-1698693815425.png

 

 

Here the DAX Measure:

Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    OR ( 
        'Table'[Preferred] = 'Table'[Sorted], 
        'Table'[Sorted] <> BLANK() && 'Table'[Preferred] = BLANK()
    )
)

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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
Super User
Super User

@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] )


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.