Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.