cancel
Showing results for
Did you mean:
Helper I

## How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

I have data which looks like this:

```ID	numMinutes	dayTime
00b02	7	7/11/2019
00b02	14	7/11/2019
00b02	32	7/10/2019
00b02	18	7/10/2019
00b02	41	7/21/2019
00b02	15	7/21/2019
00b02	5	7/15/2019
00b02	2	7/14/2019
00c59	10	8/29/2019
00c59	4	8/29/2019
00c59	18	7/29/2019
00c59	6	7/31/2019
00c59	13	8/1/2019
00c59	10	9/27/2019
00c59	3	8/2/2019
00c59	2	8/13/2019
00c59	2	7/2/2019
00c59	6	9/28/2019
0121d	3	9/22/2019
0121d	9	8/15/2019
0121d	1	8/15/2019
0121d	16	8/27/2019```

I need to find the distinct count of dayTime per ID and sum these distinct counts. Then sum the numMinutes and divide the sum of distinct counts of dayTime to work out the average numMinutes spent per day.

I tried using summerize to created a table of counts per ID like follows:

`daycount = SUMMARIZE(AccountUsage,AccountUsage[Id],"daycount",DISTINCTCOUNT(AccountUsage[dayTime]))`

Then sum the result daycount column as totaldaycount.

This works great when I don't put a date slicer. Once I start changing dates on the slicer, the results become wrong. It seems the Summarize table does not interact with Date Slicer. Is there a way to make this work or make the calculations I desire?

Edit: It seems the Summarize method can achieve my calculation. But the sumMinutes needed to be SUM'ed with a FILTER that does a ALLSELECTED(numMinutes),ISONORAFTER(numMinutes),MAX(numMinutes), which @Ashish_Mathur indicated.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and for your case, Just try this formula to create a measure

```Measure = var _table=SUMMARIZE(AccountUsage,AccountUsage[ID],"_discount",CALCULATE(DISTINCTCOUNT(AccountUsage[dayTime])) ) return
SUMX(_table,[_discount])```

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi,

Is this the result you are expecting?  You may download my PBI file from here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Memorable Member

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

http://www.mediafire.com/file/5ynjl27hibhk864/OneOkCat-Distinct_Count_by_ID_with_Date_Slicer.pbix

If it is not the result, can you share expected result with the sample data.

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Helper I

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi @venal ,

For the CountMinutes column, I want to have the SUM of the distinct count of the dates.

```ID	CountMinutes
00b02	3
00c59	1
0121d	1
00b02	2
00c59	8
0121d	2
17	total```

Can you please show me how it's done? Many thanks. In the mean while I'll modify the codes you've provided and see if I can work something out.

Note. I tried your measures with my real data. The sumMinutes is correct at 314369 but countMinutes is 188 but should be 13294

Community Support

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and for your case, Just try this formula to create a measure

```Measure = var _table=SUMMARIZE(AccountUsage,AccountUsage[ID],"_discount",CALCULATE(DISTINCTCOUNT(AccountUsage[dayTime])) ) return
SUMX(_table,[_discount])```

Best Regards,

Lin

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

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

I have done the research and read through many posts regarding sum of measures. All of them don't work with Date Slicers. The Summarized total would stay the same even if you change the dates. I have used the exact formular that you've proposed many days ago to no avail.

Community Support

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat

It should work well, there should be something wrong in other.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi,

Is this the result you are expecting?  You may download my PBI file from here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

I used your way but as you can see it give the correct individual distinct count per accountId but not the total. I need find the SUM of these distinct counts. The total should be 13294 instead of 90.

PS Thanks everyone for the contribution so far. I think we are getting close.

Community Support

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat

It is a measure total problem, not sure why the above formula doesn't work, could you share a simple sample pbix file for us have a test?

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

## Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors