Forum Discussion

frw's avatar
frw
Frequent Visitor
3 years ago
Solved

Counting Distinct IDs with Non-Blank/Zero Values Elsewhere

Hello everyone, I've been banging my head against the wall for the last few days trying to solve something that I feel like should be easy. Goal: Measure that will return the number of products p...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    frw,

     

    Try this measure:

     

    Products Sold = 
CALCULATE (
    DISTINCTCOUNT ( Summary[Item ID] ),
    ALLSELECTED ( 'Date'[Date] ),
    Summary[Sold] > 0,
    NOT ISBLANK ( Summary[Sold] )
)

     

     

DataInsights's avatar
DataInsights
Super User
3 years ago

frw,

 

Try this measure:

 

Products Sold = 
CALCULATE (
    DISTINCTCOUNT ( Summary[Item ID] ),
    ALLSELECTED ( 'Date'[Date] ),
    Summary[Sold] > 0,
    NOT ISBLANK ( Summary[Sold] )
)

 

 

frw's avatar
frw
Frequent Visitor
3 years ago

Hey DataInsights,

 

Thanks for the reply. You got me almost all the way there; when I broke the category matrix (like your image) down into the member products, it was still showing 1 for each of the individual products with any sales. I added the following to the filter context, and it propogated the "Products Sold" measure through for each item in the category.

REMOVEFILTERS('SUMMARY'[ITEM ID])

 Haven't spent much time playing around with the ALL* functions in filter context, so clearly I've got some more work to do. Thanks for your help!