cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cgkas Member
Member

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

3 REPLIES 3
cgkas Member
Member

Re: Dax formula to count elements with multiple conditions

May someone could help me with this question please.

Community Support Team
Community Support Team

Re: Dax formula to count elements with multiple conditions

Hi @cgkas ,

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
cgkas Member
Member

Re: Dax formula to count elements with multiple conditions

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 285 members 2,581 guests
Please welcome our newest community members: