Dax formula to count elements with multiple conditions

[cgkas]

Good day everyone,

Maybe someone could help me. 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 in advance for any help

[v-eachen-msft]

Hi @cgkas ,

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

[cgkas]

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

[cgkas]

May someone could help me with this question please.