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.
