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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
PowerBIUser27
Helper I
Helper I

DAX Count of matches through contradicting conditions

I have a DAX issue I've been unable to solve via search so far. I need the distinct count of [ItemCode] below that fulfills two contradicting criteria at the same time: 

  • Category = New, CategoryDetails = Blue
  • Category = Used, CategoryDetails = Black

Below, ItemCode 00001 fulfills these conditions in its first two rows.

 

Sample data:

ItemCode CategoryCategoryDetails
00001NewBlue
00001UsedBlack
00001UsedBlue
00001UsedBlue
00002NewBlue
00002UsedBlack
00002UsedBlue
00003NewBlack
00003NewBlack
00003NewBlack
00003NewBlack

 

The result from correct code should be 2, as [ItemCode] 00001 and 00002 fulfill the conditions, but 00003 does not. So 2 total here. 

 

My issue is that using AND(), OR(), and IN() don't seem to work and just return 0. Something like [Category] = "New" && [Category] = "Used" obviously doesn't work.

 

Any help writing this in a DAX measure?

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

ThxAlot_0-1720818932353.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

This measure returns 2 as the Answer

Measure = CALCULATE(DISTINCTCOUNT(Data[ItemCode ]),((Data[Category]="New"&&Data[CategoryDetails]="Blue")||(Data[Category]="used"&&Data[CategoryDetails]="black")))

Hope this helps.

Ashish_Mathur_0-1720844066672.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ThxAlot
Super User
Super User

ThxAlot_0-1720818932353.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Wow thank you, just one question! How could I add a negative condition with the positive conditions? Example: right now the conditions are CategoryDetails = some color. How would I change those to be exclusive instead of inclusive? Currently, the conditions are:

  • Category = New, CategoryDetails = Blue
  • Category = Used, CategoryDetails = Black

What if I needed to set it to:

  • Category = New, CategoryDetails NOT = Blue
  • Category = Used, CategoryDetails NOT = Black

Thank you!!

ThxAlot_1-1720901983095.png

 

For fun only, to showcase the powerful Excel formulas,

ThxAlot_0-1720946093309.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Hello @PowerBIUser27 

 

the simplest way is adding NOT in your DAX to make it opposite value.

Below just for example:
Original Value

Irwan_0-1720829709086.png

 

Adding NOT

Irwan_1-1720829768589.png

 

Hope this will help you.

Thank you.

JK_Bhuvaraghan
New Member

Try this:

 

CALCULATE (
    DISTINCTCOUNT ( 'Table'[ItemCode ] ),
    OR (
        AND ( 'Table'[Category] = "New", 'Table'[CategoryDetails] = "Blue" ),
        AND ( 'Table'[Category] = "Used", 'Table'[CategoryDetails] = "Black" )
    )
)

Unfortunately that isn't working, I get very incorrect numbers with that formula. 

 

To clarify: that formula treats the conditions with an "OR" statement. I need a count of items that have at least one row fulfilling each of the conditions.  So if a dozen rows fulfill the first condition, but none fulfill the second, no go.

 

For example, if I used that formula, and all rows fulfilled the second condition ([Category] = "Used" and all [CategoryDetails] = "Black"), those rows would be counted. But here we need both condition statements to be true, not just one, to increase the count. Does that help?

Thus, my issue. I can't use OR, and I can't use AND. Not sure what to do.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.