DAX Count of matches through contradicting conditions

[PowerBIUser27]

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?

[Ashish_Mathur]

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.

[ThxAlot · Accepted solution]

[PowerBIUser27]

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]

For fun only, to showcase the powerful Excel formulas,

[Irwan]

Hello @PowerBIUser27 

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

Below just for example:
Original Value

Adding NOT

Hope this will help you.

Thank you.

[JK_Bhuvaraghan]

Try this:

CALCULATE (

    DISTINCTCOUNT ( 'Table'[ItemCode ] ),

    OR (

        AND ( 'Table'[Category] = "New", 'Table'[CategoryDetails] = "Blue" ),

        AND ( 'Table'[Category] = "Used", 'Table'[CategoryDetails] = "Black" )

    )

)

[PowerBIUser27]

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.