Forum Discussion

ajaydavidluke's avatar
ajaydavidluke
Helper I
4 years ago

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.

 

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

4 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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
  )
)
    • ajaydavidluke's avatar
      ajaydavidluke
      Helper I
      4 years ago

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

       

      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

       

      • daXtreme's avatar
        daXtreme
        Solution Sage
        4 years ago

        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.