Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Poupouh
Frequent Visitor

Distinctcount excluding negative number through time

Hello!

I am quite new to Power Bi and I am currenlty experiencing trouble with the DISTINCTCOUNT  function. 

What I am trying to do is quite easy, counting the distinct number of customer excluding the negative and nul numbers. 

My formula is the follwing: 

 

calculate( distinctcount(Database[Parent name]),filter('Database','Database'[transaction sales]>=1), filter('calendar','Calendar'[Year Type]="This Year"))
 
My expected final visual must be as followed
Picture1.png
However for some reason Power Bi count the customers as long as they have a positive amount once. Which results in a deviation in the final number of customers per month. 
Picture2.png
I don't know how to chnage my formula to make Power Bi iterate on a monthly basis not on the total. 
 
If any body could be of assistance on this point this would be really really helpful. 
Thank you in advance 😣
 
 
1 ACCEPTED SOLUTION
Poupouh
Frequent Visitor

Thank you for the reference. I managed getting the good number via a If and Summarize function. 

Again, thank you for your help!

View solution in original post

2 REPLIES 2
Poupouh
Frequent Visitor

Thank you for the reference. I managed getting the good number via a If and Summarize function. 

Again, thank you for your help!

amitchandak
Super User
Super User

@Poupouh , distinctcount will be calculated again at grand total so it will not be a sum of rows

 

refer: https://www.youtube.com/watch?v=ufHOOLdi_jk

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.