cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Category CategoryDetails 00001 New Blue 00001 Used Black 00001 Used Blue 00001 Used Blue 00002 New Blue 00002 Used Black 00002 Used Blue 00003 New Black 00003 New Black 00003 New Black 00003 New Black

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Helper I

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!!

Super User

For fun only, to showcase the powerful Excel formulas,

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Solution Specialist

Hello @PowerBIUser27

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

Below just for example:
Original Value

Thank you.

New Member

Try this:

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.