## Problem With Using Countx in DAX[Solved]

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

### Problem With Using Countx in DAX[Solved]

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
Sample.jpg (199.81 KiB) Viewed 331 times
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

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

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

### Re: Problem With Using Countx in DAX

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

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

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

### Re: Problem With Using Countx in DAX

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

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

### Re: Problem With Using Countx in DAX

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

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

### Re: Problem With Using Countx in DAX

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.