Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a list of IDs and timestamps. I need to do a distinct count of IDs by hour and then average each hour's count in a measure. I'd prefer not to create separate tables because I need to do this for several indicator cars using different columns so I want to be able to do the group by inside a measure.
Source Data
Hour | ID |
8:00 AM | A |
8:00 AM | A |
9:00 AM | A |
9:00 AM | D |
9:00 AM | E |
10:00 AM | A |
10:00 AM | G |
10:00 AM | H |
10:00 AM | I |
10:00 AM | J |
11:00 AM | K |
11:00 AM | L |
12:00 PM | M |
Inbetween Table if I had to set up a separate table to do the calculation
Hour | Distinct Count of ID |
8:00 AM | 1 |
9:00 AM | 3 |
10:00 AM | 5 |
11:00 AM | 3 |
Final Indicator Card Value that I want to create
Average of Distinct Count of ID | 3 |
Is there a way for me to do this without having to create the inbetween table? My final visual is an indicator card so I can't use a table to group by the measure
Solved! Go to Solution.
Hi @BriefStop
You can achieve your goal with 2 simple measures:
1. For the distinct count of IDS by Hour:
2. For Fixing "wrong" total :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi,
These measures work
ID count = DISTINCTCOUNT(Data[ID])
Measure = AVERAGEX(VALUES(Data[Hour]),[ID count])
Hope this helps.
Hey @Ashish_Mathur ,
can you please explain the advantage of your solution over mine. Your DAX contains more or less the same code except that is split across two measures, instead of using only one.
I'm always eager to learn and improve.
Regards,
Tom
You are correct. My post indeed does not add any value at all. Sorry about the duplicate post.
Hi @BriefStop
You can achieve your goal with 2 simple measures:
1. For the distinct count of IDS by Hour:
2. For Fixing "wrong" total :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hey @BriefStop ,
this measure helps to create what you want:
Measure =
AVERAGEX(
VALUES( 'Table'[Hour] )
, CALCULATE( DISTINCTCOUNT('Table'[ID] ) )
)
But I'm not sure about the expected result, because I get 2.4 instead of the expected 3:
The measure returns the same result in the Total line (no filter context does it's magic here) and the Card visual.
The measure iterates across a table of hours and determines the average by dividing the numeric expression . by the number of rows. You have to be aware that the number of rows is 1 inside the table visual but five at the total line.
Hopefully, this provides what your are looking for to tackle your system.
Regards,
Tom
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |