Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |