March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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:
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?
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
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:
What if I needed to set it to:
Thank you!!
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.
Try this:
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
91 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |