cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Count occurrences of values based on a slicer

Hi,

 

I have a table that contains ID, DATE and TIME. Following is an example.

 

ID      DATE       TIME

A       01/01      10:00

B       01/01      10:00

C       01/01      10:30

A       01/01      11:00

A       01/01      11:30

B       01/01      11:30

C       02/01      08:00
A       02/01      08:00

A       03/01      10:00

B       03/01      10:00

C       03/01      10:30

D       03/01      10:30

D       03/01      11:00

 

 

I need to have the number of occurrence of each ID, but this number should be based on a Date slicer. For example, if the slicer selects the days 1, 2 and 3, I would like to have this as result:

 

Occurrence     Number of IDs

3x                        2                     (Shows me the number of IDs that appear 3 times - B and C)

5x                        1                     (Shows me the number of IDs that appear 5 times - A)

 

 

On the other hand, when I set the slicer to select just the day 3, for example, I want to have this as result:

 

Occurrence     Number of IDs

1x                          3

2x                          1

 

 

 

What did I get so far? I know that I can create a Calculated Column that shows the number of occurrence of the IDs, it gives me the table that I want as result but it doesn't get affected by the slicer. I also know that I can create a Measure that counts how many times an ID appear and gets affected by the slicer, but it doesn't give me a table as shown before as a result. When all the days are selected by the slicer, it gives me a table like this:

 

ID    Occurrence

A           5

B           3

C           3

D           2

 

Does anyone know how could I have the table that I want as result that gets affected by a Date slicer?

 

Thanks in advance,

Cheers.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

@rafaelols,

 

You may refer to the following thread.

https://community.powerbi.com/t5/Desktop/Aggregates-of-counts-over-time/td-p/281500

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

@rafaelols,

 

You may refer to the following thread.

https://community.powerbi.com/t5/Desktop/Aggregates-of-counts-over-time/td-p/281500

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

View solution in original post

Highlighted

@v-chuncz-msft

 

Awesome!

 

Your response in that thread is exactly what I was looking for. For some reason I couldn't find that thread at my searchings.

 

Thank you very much!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors