Forum Discussion
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 per category that had any sales in the selected time period (date slicer)
Tables: Summary table with condensed daily sales for each item (example for one day shown). As well, there is a date table with 1:* relationship to the Date column of the below table.
| Date | Item ID | Classification | Sold |
| 1/1/2023 | 1 | CSR | 5 |
| 1/1/2023 | 2 | CSR | 7 |
| 1/1/2023 | 3 | CSR | 0 |
| 1/1/2023 | 4 | BCSR | 3 |
| 1/1/2023 | 5 | BCSR | 4 |
| 1/1/2023 | 6 | BCSR | 1 |
| 1/1/2023 | 7 | BUS | 24 |
| 1/1/2023 | 8 | BUS | 7 |
| 1/1/2023 | 9 | BUS | 0 |
| 1/1/2023 | 10 | RES | 3 |
| 1/1/2023 | 11 | RES | 4 |
| 1/1/2023 | 12 | RES | 1 |
The goal is to have a matrix/table visual that, in each row, will display the total number of products that had any sales in the time period specified by the date slicer. For example, the measure for the above data would show:
CSR: 2, BCSR: 3, BUS: 2, RES: 3
After playing around for a while and exhausting my Google-fu, I've got two measures that handle half of the problem but don't get me where I need.
Products Sold 1 =
CALCULATE (
DISTINCTCOUNT ( 'Summary'[Item ID] ),
ALLEXCEPT ( 'Summary', 'Summary'[Classification] ),
ALLEXCEPT ( 'Date', 'Date'[Date] ),
FILTER ( 'Summary', SUM ( 'Summary'[Sold] ) > 0 )
)
Products Sold 2 =
VAR tab =
CALCULATETABLE (
FILTER ( 'Summary', SUM ( 'Summary'[Sold] ) > 0 ),
ALLEXCEPT ( 'Summary', 'Summary'[Classification] ),
ALLEXCEPT ( 'Date', 'Date'[Date] )
)
VAR result =
CALCULATE ( DISTINCTCOUNT ( 'Summary'[Item ID] ), tab )
RETURN
result
The first measure correctly counts the number of products in a classification with any sales in the time period, but it assigns a value of 1 for each item with a sale. The second, meanwhile, gives the same value for each item in the matrix as desired, but it also counts the items without sales. Preferrably, the below would show a 37 everywhere you see a 1 currently, although the alternative with 37 appearing in every row is also acceptable.
I know I must be missing something easy, but I've tried so many permutations of found DAX online that I'm starting to lose my mind.
frw,
Try this measure:
Products Sold = CALCULATE ( DISTINCTCOUNT ( Summary[Item ID] ), ALLSELECTED ( 'Date'[Date] ), Summary[Sold] > 0, NOT ISBLANK ( Summary[Sold] ) )
2 Replies
frw,
Try this measure:
Products Sold = CALCULATE ( DISTINCTCOUNT ( Summary[Item ID] ), ALLSELECTED ( 'Date'[Date] ), Summary[Sold] > 0, NOT ISBLANK ( Summary[Sold] ) )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!
