Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ajaydavidluke
Helper I
Helper I

DAX - Distinct Count on zero value from table

Good morning community,

 

Below is a tableI have which indicates stock in each location (Total Stock Qty = Both Warehouses)  I'm looking to create a seperate card visual which will show a total count of all the locations which have a zero value but cant seem to string the right DAX together.

 

ajaydavidluke_0-1660905746484.png

If anyone could suggest the correct path here that would be greatly appriciated 🙂

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

You have not shown the model, so I have to work with what you've shown...

[# Locations with 0 StQty] =
COUNTROWS(
  FILTER(
    DISTINCT( T[Location] ),
    [Total stock qty] = 0
  )
)

Hi and thank you for your reply, I have inserted the below which is very similiar to my solution and have pretty similar results

ajaydavidluke_1-1660907700453.png

 

But when I insert the dax into my card I get a massively inflated value, as you can visually count the answer should be 13 as there are only 13 that have zero

ajaydavidluke_0-1660907679946.png

 

@ajaydavidluke 

 

If this does not work, it means something else is at play here. But you're not showing everything that's essential. DAX output depends heavily on the model and the relationships between tables. Therefore the same formula can return different results if the underlying data model is different. I wrote the measure with assumptions that may be wrong in this particular instance. You'll have to either show the model, or - better still - give us a link to a shared file with some example data. Then one can with certainty tell you where it's gone wrong. Otherwise, it's a mere guessing.

Sure thing, let me explain a bit more about the relationship as the data is simple:

ajaydavidluke_1-1660911682207.png

We have 2 brands within the same warehouse so I have extracted the data from 2 seperate databases and exported them to excel.

The link between the 2 in this instance is the Location.  Location Planner WA development is the main table and the Location Planner GA WA development shares the same locations.

ajaydavidluke_2-1660911827902.png

 

I have created a a column which pulls in the value from GA table 

-GA Stock Qty = RELATED('Location Planner GA WA development'[DL Stock  Qty])-
 
I then add the 2 values together:
Total stock qty = 'Location Planner WA development'[DL Stock  Qty] + 'Location Planner WA development'[GA Stock Qty]
 
and this is then refrenced in the final zero calculation as sugessted:
Empty WA Locations =
COUNTROWS(
  FILTER(
    DISTINCT( 'Location Planner WA development'[Location] ),
    'Location Planner WA development'[Total stock qty] = 0
  )
)

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.