Problem With Using Countx in DAX[Solved]

Anything related to PowerPivot and DAX Formuale
shahvaladi
Posts: 4
Joined: Fri Nov 06, 2020 11:57 pm

Problem With Using Countx in DAX[Solved]

Postby shahvaladi » Sat Nov 07, 2020 2:05 am

Hello friends,

I have created a data model in excel and trying to calculate the distinct count of customers in my model.
What I want is not just a simple distinct count. I want to count the customers with the positive sales Qty.

I have found a way to create a measure which works fine in calculating the desired answer. But I want to have another measure to calculate the "number of active customers in Category" in case that I have the products in my pivot table.

I will attach my data model for you in case you need to check my model.

I used this formula to calculate the Active customers with positive sales qty: "COUNTX(FILTER(ALLSELECTED(Table1[Customer Id]),Table1[Sum of Qty]>0),[Sum of Qty])"

I will be so thankful if you guys could help me in using dax "All" or whatever needed function in my measure to solve my problem
Attachments
Data Model Sample.xlsx
(164.04 KiB) Downloaded 16 times
Sample.jpg
Sample.jpg (199.81 KiB) Viewed 331 times
calc.jpg
calc.jpg (87.87 KiB) Viewed 331 times
Last edited by shahvaladi on Wed Nov 11, 2020 6:13 pm, edited 1 time in total.

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Problem With Using Countx in DAX

Postby MattAllington » Sat Nov 07, 2020 7:21 am

You should never post the same question in 2 forums. It disrespects the effort that someone may put in to help you with an in answered question when it has actually been solved in a different forum
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

shahvaladi
Posts: 4
Joined: Fri Nov 06, 2020 11:57 pm

Re: Problem With Using Countx in DAX

Postby shahvaladi » Sat Nov 07, 2020 7:26 am

MattAllington {L_WROTE}{L_COLON}You should never post the same question in 2 forums. It disrespects the effort that someone may put in to help you with an in answered question when it has actually been solved in a different forum

I did not know that is a violation of rules. It would be appreciated if you could help me figure out to solve my problem friend.

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Problem With Using Countx in DAX

Postby MattAllington » Sun Nov 08, 2020 6:06 am

It’s not so much a “rule” but a general courtesy across forums. Please provide a link here to the other post so I can be sure no one else has answered it already
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

shahvaladi
Posts: 4
Joined: Fri Nov 06, 2020 11:57 pm

Re: Problem With Using Countx in DAX

Postby shahvaladi » Sun Nov 08, 2020 6:50 am

MattAllington {L_WROTE}{L_COLON}It’s not so much a “rule” but a general courtesy across forums. Please provide a link here to the other post so I can be sure no one else has answered it already

I just wanted to have my answer as fast as I could. No one has answered my question :(
https://www.excelguru.ca/forums/showthr ... ntx-in-DAX
https://www.mrexcel.com/board/threads/p ... x.1150439/

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Problem With Using Countx in DAX

Postby MattAllington » Tue Nov 10, 2020 7:14 am

Here are the measures I wrote

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

Active Customers=SUMX(values(Table1[Customer Id]),if([Sum of Qty]>0,1))
Category Count =if([Active Customers]>0,CALCULATE([Active Customers],ALL(Table1[Product])))
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

shahvaladi
Posts: 4
Joined: Fri Nov 06, 2020 11:57 pm

Re: Problem With Using Countx in DAX

Postby shahvaladi » Tue Nov 10, 2020 4:15 pm

MattAllington {L_WROTE}{L_COLON}Here are the measures I wrote

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

Active Customers=SUMX(values(Table1[Customer Id]),if([Sum of Qty]>0,1))
Category Count =if([Active Customers]>0,CALCULATE([Active Customers],ALL(Table1[Product])))

Wow, Works Like a charm!
Tnx Mat that was really what I needed.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests