Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - I have the following date (abbreviated sample). I count by Event Key (table is called 'WBR'). I'd like to produce a measure which calculates the highest maximum count, across all the dates. I'd then like to somehow display the Date, to which this maximum count occurs on.
In my sample, the Maximum count (based on Event Key) is 6, and this occured on 01/01/2022.
Really appreciate your help on what it probably quite simple but it's tying me up in knots at the moment! Many thanks everyone.
Event Key | Stat Date |
4432119 | 01/01/2022 |
4798904 | 01/01/2022 |
5426796 | 01/01/2022 |
5483090 | 02/02/2022 |
5289897 | 02/03/2022 |
4245429 | 02/03/2022 |
4366550 | 04/01/2022 |
7317690 | 05/01/2022 |
4494973 | 06/01/2022 |
6560468 | 07/01/2022 |
4722334 | 08/01/2022 |
5770131 | 09/03/2022 |
5323040 | 10/02/2022 |
6033420 | 01/01/2022 |
5210188 | 01/01/2022 |
4364367 | 01/01/2022 |
Solved! Go to Solution.
Hi, @Creative_tree88
You can try the following methods.
Measure:
Count = CALCULATE(COUNT('Table'[Stat Date]),ALLEXCEPT('Table','Table'[Stat Date]))
Max Count = MAXX(ALL('Table'),[Count])
Max Count Date = CALCULATE(MAX('Table'[Stat Date]),FILTER(ALL('Table'),[Count]=[Max Count]))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Creative_tree88
You can try the following methods.
Measure:
Count = CALCULATE(COUNT('Table'[Stat Date]),ALLEXCEPT('Table','Table'[Stat Date]))
Max Count = MAXX(ALL('Table'),[Count])
Max Count Date = CALCULATE(MAX('Table'[Stat Date]),FILTER(ALL('Table'),[Count]=[Max Count]))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MAXX(
VALUES('WBR'[Stat Date]),
CALCULATE( COUNTROWS('WBR') )
)
Give it a thumbs up and accept as solution if this is helpful!
@YukiK - that works. But how do I create a second measure to show me the date that this MAX corresponds to? In my example above it would be 01/01/2022. Many thanks!
Your Measure =
VAR _Cnt = CALCULATE( COUNTROWS('WBR') )
VAR _MaxDate =
FILTER(
VALUES('WBR'[Stat Date]),
CALCULATE( COUNTROWS('WBR') ) = _Cnt
)
RETURN
_MaxDate
This should work, though, I'd have "COUNTROWS('WBR')" as a separate measure and reference it in other measures.
@YukiK - this doesn't seem to work. Power BI doesn't return any date, just 'Blank'. Any ideas?
Oops. I just referenced a wrong thing. Replace the first variable with this one:
VAR _Cnt = [Max Cnt Measure you just created]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
101 | |
75 | |
65 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |