Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
6 years ago

Dax formula to count elements with multiple conditions

Good day everyone,

Maybe someone could help me. I have the following table

IDSTOREMODELSIZECODE_NUMRCODESCODE
0STORE_08DIAMONDTALL502609MENMTGGTG
0STORE_08PLATINUMTALL1312314WOMENACGUSG
1STORE_09DIAMONDSHORT775WOMENDMEHNE
2STORE_10DIAMONDSMALL775WOMENDMEHNE
3STORE_11PLATINUMSTANDARD1989707MENUD1DE1
3STORE_11PLATINUMSTANDARD532715WOMENRCNGBN
3STORE_11PLATINUMSTANDARD502991MENMTGGTG
3STORE_11PLATINUMTALL120631MENAW6US6
3STORE_11PLATINUMTALL1320334MENNOT IN STOCK
3STORE_11PLATINUMTALL31234MENUD1DE1
3STORE_11PLATINUMTALL47259MENMTGGTG


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 in advance for any help

Need Help

3 Replies

  • cgkas's avatar
    cgkas
    Icon for Helper V rankHelper V
    6 years ago

    May someone could help me with this question please.

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi cgkas ,

     

    You could replace "VALUES" with "SELECTEDVALUE" because "VALUES" will return multi values in your case.

     

    • cgkas's avatar
      cgkas
      Icon for Helper V rankHelper V
      6 years ago

      Hello, thanks for your answer.

       

      I changed to SELECTEDVALUE, but is always showing 8, when should be 6.

       

       
      IF(SELECTEDVALUE(PRODS[SCODE])<>"NOT IN STOCK",
DISTINCTCOUNT(PRODS[SCODE]))
      Besides that, I'm far to get the desired output I'm looking for that is show the count of unique (distincts) SCODES when ID = 5, RCODE = MEN and MODEL = PLATINUM using DAX formula.
       
      May you help me with this?
       
      Thanks