# How to get count of distinct values for mutiple criterias DAX formula?

• ### Question

• Hello to all,

I opened this question on Power Query forum, but t seems is more approapiate in Power Pivot I think.

I have the following table

 ID STORE MODEL SIZE CODE_NUM RCODE SCODE 0 STORE_08 DIAMOND TALL 502609 MEN MTGGTG 0 STORE_08 PLATINUM TALL 1312314 WOMEN ACGUSG 1 STORE_09 DIAMOND SHORT 775 WOMEN DMEHNE 2 STORE_10 DIAMOND SMALL 775 WOMEN DMEHNE 3 STORE_11 PLATINUM STANDARD 1989707 MEN UD1DE1 3 STORE_11 PLATINUM STANDARD 532715 WOMEN RCNGBN 3 STORE_11 PLATINUM STANDARD 502991 MEN MTGGTG 3 STORE_11 PLATINUM TALL 120631 MEN AW6US6 3 STORE_11 PLATINUM TALL 1320334 MEN NOT IN STOCK 3 STORE_11 PLATINUM TALL 31234 MEN UD1DE1 3 STORE_11 PLATINUM TALL 47259 MEN MTGGTG

I'd like to get the count of unique (distincts) SCODES when ID = 5, RCODE = MEN and MODEL = PLATINUM

If in SCODE there are values = NOT IN STOCK then should be counted apart. Then the output I'm looking for is like this:

```Total Distincts = 3
UD1DE1,MTGGTG,AW6US6
NOT IN STOCK = 1
1320334```

If NOT IN STOCK = 0 and Total Distincts = 0 then the output would be

`Total Distincts = 0`

If NOT IN STOCK = 0 then and Total Distincts > 0 the output would be

`Total Distincts = N (Where N > 0)`

I've tried a measure like below but is not working

```Measure = IF(VALUES(PRODS[SCODE]<>"NOT IN STOCK"),
DISTINCTCOUNT(PRODS[SCODE]),
COUNTAX(PRODS[SCODE],PRODS[SCODE]="NOT IN STOCK"))```

Thanks for any help

• Edited by Thursday, October 10, 2019 4:48 PM
Thursday, October 10, 2019 4:46 PM

### All replies

• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Count distinct SCode with PQ.
Pulled "Not in stock" in SCode into separate "Qty" column.
http://www.mediafire.com/file/57sm1f247uzi7fx/10_11_19.xlsx/file
http://www.mediafire.com/file/5unuv7zeh0mffvs/10_11_19.pdf/file

Friday, October 11, 2019 5:25 PM
• Hi Herbert,

I was testing your solution, even is not in DAX, but in M code is works nice.

My final goal is to show the count of uniques "SCODE" for each ID, MODEL and RCODE and the SCODEs concatenated in a single string for example table below only showing ID 0 and 1, MODEL Jar and Mof for RCODE F and M.

``````+---+---------------------+---------------------+----------- -----+---------------------+
|ID |                     F                     |                 M                     |
+---+---------------------+---------------------+----------- -----+---------------------+
|   |         Jar         |         Mof         |         Jar     |         Mof         |
+---+---------------------+---------------------+----------- -----+---------------------+
| 0 | Total: 0            | Total: 1            | Total: 0        | Total: 1            |
|   |                     |     ACGUSG          |                 |     MTGGTG          |
+---+---------------------+---------------------+----------- -----+---------------------+
| 1 | Total: 5            | Total: 5            | Total: 4        | Total: 5            |
|   |                     |                     |                 |                     |
+---+---------------------+---------------------+----------- -----+---------------------+``````
``May you help me with M language code to do this if it is easier for you? ``Thanks in advance.``

• Edited by Saturday, October 12, 2019 7:24 AM
Saturday, October 12, 2019 7:16 AM
• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Very M and DAX code intensive.
Recommend book:
"Collect, Combine and Transform data using PQ in Excel & PBI" by Gil Raviv.
ETA: 3 months.

Sunday, October 13, 2019 4:28 AM
• Hi Herbert,

Thanks for your help. I was trying to reproduce your solution to try to understand and  almost finish, but I don't know how do you set the final pivot table in order to have the Table1 and Table7 associated in Pivot Table. Because of that I think that MODEL column is in the same column of ID when I try to create the pivot table. In yours ID and MODEL are in different column.

Tuesday, October 15, 2019 5:00 AM
• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Table1 and Table7 were merged in PQ.
See Chapter 9 "Merging Tables and Queries" in this book:
"M is for (Data) Monkey" by Miguel Escobar.
Same info in the previously mentioned book.
PowerPivot will do the same thing, using "Relationships" between Tables.
See this book:
"Building Data Models with PowerPivot" by Ferrari and Russo.
Another 3 months.

Tuesday, October 15, 2019 6:51 PM
• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Table1 and Table7 were merged in PQ.
See Chapter 9 "Merging Tables and Queries" in this book:
"M is for (Data) Monkey" by Miguel Escobar.
Same info in the previously mentioned book.
PowerPivot will do the same thing, using "Relationships" between Tables.
See this book:
"Building Data Models with PowerPivot" by Ferrari and Russo.
Another 3 months.