none
How to get count of distinct values for mutiple criterias DAX formula? RRS feed

  • 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 cgkmal 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,

    Many thanks for your help.

    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            |
    |   |                     |                     |                 |                     |
    |   | NAD,VID,DOK,FIL,HUB | NAD,HUB,FIL,VID,DOK | HUB,VID,NAD,FIL | NAD,FIL,HUB,VID,DOK |
    +---+---------------------+---------------------+----------- -----+---------------------+

    May you help me with M language code to do this if it is easier for you?

    Thanks in advance.




    • Edited by cgkmal 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)
    Added your 2nd separate goal to files in my links.
    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.

    Thanks Herbert for your help!
    Tuesday, October 15, 2019 7:56 PM