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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
morther
Helper I
Helper I

How to count distinct Months in a column

I have a slicer in PowerBI which I can select multiple months.

 

I added a Card in PowerBI with the "Month" selected and choose (Count Distinct). When I select multiple months in the slicer, PowerBI returns the exact count of months that I selected. If I choose August, September, October, the count is "3" just as expected.

 

I need to create a Column that multiplies "130 * DISTINCTCOUNT(ImportCubicPM[Month]"

 

test = CALCULATE(130 * (
     DISTINCTCOUNT(ImportCubicPM[Month])))

 

If 2 months are selected by the slicer then the total should be 260.

 

The code above is not calculating properly.  The numbers are off a little bit.  

 

I am not sure if I should be using DISTINCTCOUNT

 

Any help would be greatly appreciated.

Thanks, Mike

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

 

What result do you get with this?

 

=130*DISTINCTCOUNT(ImportCubicPM[Month])

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Eric_Zhang
Employee
Employee


@morther wrote:

I have a slicer in PowerBI which I can select multiple months.

 

I added a Card in PowerBI with the "Month" selected and choose (Count Distinct). When I select multiple months in the slicer, PowerBI returns the exact count of months that I selected. If I choose August, September, October, the count is "3" just as expected.

 

I need to create a Column that multiplies "130 * DISTINCTCOUNT(ImportCubicPM[Month]"

 

test = CALCULATE(130 * (
     DISTINCTCOUNT(ImportCubicPM[Month])))

 

If 2 months are selected by the slicer then the total should be 260.

 

The code above is not calculating properly.  The numbers are off a little bit.  

 

I am not sure if I should be using DISTINCTCOUNT

 

Any help would be greatly appreciated.

Thanks, Mike


@morther

The DAX measure shall work If it is a measure. If you actually created a "Column" in your case, try to create a measure instead. Otherwise, for further suggestion, please post more sample data of your case. Or even better you can upload the pbix file to Onedrive/Google drive and share the link.

View solution in original post

5 REPLIES 5
Eric_Zhang
Employee
Employee


@morther wrote:

I have a slicer in PowerBI which I can select multiple months.

 

I added a Card in PowerBI with the "Month" selected and choose (Count Distinct). When I select multiple months in the slicer, PowerBI returns the exact count of months that I selected. If I choose August, September, October, the count is "3" just as expected.

 

I need to create a Column that multiplies "130 * DISTINCTCOUNT(ImportCubicPM[Month]"

 

test = CALCULATE(130 * (
     DISTINCTCOUNT(ImportCubicPM[Month])))

 

If 2 months are selected by the slicer then the total should be 260.

 

The code above is not calculating properly.  The numbers are off a little bit.  

 

I am not sure if I should be using DISTINCTCOUNT

 

Any help would be greatly appreciated.

Thanks, Mike


@morther

The DAX measure shall work If it is a measure. If you actually created a "Column" in your case, try to create a measure instead. Otherwise, for further suggestion, please post more sample data of your case. Or even better you can upload the pbix file to Onedrive/Google drive and share the link.

Oh my goodness, I thought I had tried that yesterday but could not get it to accept the query as a measure.

 

This worked as a measure.  Thank you so much.

 

Measure = 130*DISTINCTCOUNT(ImportCubicPM[Month])

 

Hi @morther,

 

I do not know whom you are replying to but that is exactly what my suggestion was.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

What result do you get with this?

 

=130*DISTINCTCOUNT(ImportCubicPM[Month])

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Oh my goodness, that was my bad. This was exactly the answer.

Measure = 130*DISTINCTCOUNT(ImportCubicPM[Month])

 

I was doing this as a column, but when I changed it to a measure it worked as expected.

 

I am sorry for not giving credit where credit was due.

Mike

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.