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.
DAX: Evaluate column dates and return the greatest count of events within any month within range
Hi, I am trying to write DAX for a stand-alone measure that will evaluate a column of event dates in a table , and return the greatest count of events (including duplicates) in any month contained within the column of dates. (I need to know the greatest number of events to have ever occurred within any month within the column of dates.) I know a matrix visual would accomplish this, but I need the singlular output value for a Card visual. I think this should utilize some combination of MAXX and CALCULATE but I am lost 😞
Example data below, the measure should return "18" (corresponding to April 2020). THANKS!
Event Date
1/1/2020
1/2/2020
1/4/2020
1/6/2020
1/9/2020
1/11/2020
1/15/2020
1/17/2020
1/20/2020
1/25/2020
1/29/2020
1/30/2020
1/31/2020
2/4/2020
2/8/2020
2/11/2020
2/16/2020
2/19/2020
2/24/2020
2/27/2020
2/29/2020
3/5/2020
3/7/2020
3/9/2020
3/10/2020
3/15/2020
3/18/2020
3/22/2020
3/27/2020
4/1/2020
4/2/2020
4/3/2020
4/4/2020
4/5/2020
4/6/2020
4/6/2020
4/11/2020
4/15/2020
4/15/2020
4/22/2020
4/23/2020
4/25/2020
4/26/2020
4/27/2020
4/28/2020
4/29/2020
4/30/2020
5/10/2020
5/12/2020
5/16/2020
5/19/2020
5/24/2020
5/28/2020
6/1/2020
6/4/2020
6/9/2020
6/13/2020
6/17/2020
6/20/2020
6/23/2020
6/24/2020
6/29/2020
7/3/2020
7/8/2020
7/10/2020
7/15/2020
7/17/2020
7/21/2020
7/23/2020
7/28/2020
8/1/2020
8/2/2020
8/7/2020
8/8/2020
8/11/2020
8/16/2020
8/19/2020
8/20/2020
8/25/2020
8/29/2020
8/30/2020
8/31/2020
9/2/2020
9/3/2020
9/7/2020
9/10/2020
9/11/2020
9/15/2020
9/20/2020
9/21/2020
9/26/2020
10/1/2020
10/3/2020
10/7/2020
10/8/2020
10/11/2020
10/12/2020
10/17/2020
10/18/2020
10/20/2020
10/22/2020
10/26/2020
10/31/2020
11/3/2020
11/4/2020
11/9/2020
11/10/2020
11/15/2020
11/17/2020
11/21/2020
11/26/2020
11/29/2020
12/2/2020
12/7/2020
12/9/2020
12/13/2020
12/16/2020
12/18/2020
12/20/2020
12/24/2020
12/25/2020
12/29/2020
12/30/2020
1/3/2021
1/5/2021
1/9/2021
1/11/2021
1/15/2021
1/19/2021
1/21/2021
1/23/2021
1/25/2021
1/28/2021
1/29/2021
1/31/2021
2/1/2021
2/3/2021
2/6/2021
2/9/2021
2/12/2021
2/15/2021
2/17/2021
2/20/2021
2/23/2021
2/27/2021
3/1/2021
3/3/2021
3/6/2021
3/10/2021
3/14/2021
3/18/2021
3/19/2021
3/22/2021
3/25/2021
3/28/2021
4/1/2021
4/5/2021
4/7/2021
4/8/2021
4/10/2021
4/11/2021
4/13/2021
4/14/2021
4/18/2021
4/19/2021
4/23/2021
4/26/2021
4/29/2021
5/2/2021
5/4/2021
5/6/2021
5/10/2021
5/11/2021
5/13/2021
5/17/2021
5/21/2021
5/22/2021
5/24/2021
5/27/2021
5/30/2021
5/31/2021
6/4/2021
6/5/2021
6/6/2021
6/9/2021
6/12/2021
6/16/2021
6/19/2021
6/22/2021
6/26/2021
6/28/2021
6/29/2021
7/1/2021
7/5/2021
7/7/2021
7/10/2021
7/11/2021
7/14/2021
7/16/2021
7/18/2021
7/20/2021
7/23/2021
7/25/2021
7/26/2021
7/30/2021
8/1/2021
8/3/2021
8/4/2021
8/7/2021
8/11/2021
8/12/2021
8/16/2021
8/17/2021
8/18/2021
8/21/2021
8/23/2021
8/26/2021
8/29/2021
8/30/2021
9/1/2021
9/2/2021
9/4/2021
9/5/2021
9/9/2021
9/13/2021
9/14/2021
9/15/2021
9/16/2021
9/18/2021
9/19/2021
9/21/2021
9/22/2021
9/26/2021
9/28/2021
9/30/2021
10/1/2021
10/4/2021
10/7/2021
10/10/2021
10/14/2021
10/17/2021
10/19/2021
10/23/2021
10/25/2021
10/26/2021
10/28/2021
10/31/2021
11/2/2021
11/3/2021
11/4/2021
11/6/2021
11/9/2021
11/10/2021
11/11/2021
11/14/2021
11/17/2021
11/21/2021
11/24/2021
11/28/2021
11/30/2021
12/2/2021
12/4/2021
12/8/2021
12/11/2021
12/13/2021
12/17/2021
12/20/2021
12/21/2021
12/22/2021
12/26/2021
12/27/2021
12/30/2021
1/1/2022
1/2/2022
1/4/2022
1/6/2022
1/9/2022
1/10/2022
1/14/2022
1/16/2022
1/17/2022
1/18/2022
1/20/2022
1/21/2022
1/23/2022
1/25/2022
1/26/2022
1/30/2022
2/1/2022
2/4/2022
2/8/2022
2/11/2022
2/12/2022
2/16/2022
2/20/2022
2/22/2022
2/25/2022
2/27/2022
3/2/2022
3/4/2022
3/7/2022
3/8/2022
3/12/2022
3/15/2022
3/16/2022
3/20/2022
3/22/2022
3/23/2022
3/25/2022
3/28/2022
3/29/2022
3/31/2022
4/3/2022
4/4/2022
4/5/2022
4/9/2022
4/13/2022
4/16/2022
4/17/2022
4/18/2022
4/19/2022
4/22/2022
4/25/2022
4/29/2022
4/30/2022
5/2/2022
5/4/2022
5/5/2022
5/8/2022
5/11/2022
5/14/2022
5/16/2022
5/20/2022
5/24/2022
5/27/2022
5/28/2022
5/31/2022
6/4/2022
6/7/2022
6/9/2022
6/11/2022
6/13/2022
6/14/2022
6/15/2022
6/16/2022
6/17/2022
6/19/2022
6/23/2022
6/25/2022
6/27/2022
7/1/2022
7/4/2022
7/5/2022
7/6/2022
7/9/2022
7/13/2022
7/15/2022
7/16/2022
7/19/2022
7/21/2022
7/23/2022
7/27/2022
7/29/2022
7/30/2022
7/31/2022
8/1/2022
8/3/2022
8/5/2022
8/9/2022
8/13/2022
8/15/2022
8/18/2022
8/21/2022
8/24/2022
8/25/2022
8/26/2022
8/30/2022
9/3/2022
9/5/2022
9/9/2022
9/13/2022
9/14/2022
9/18/2022
9/19/2022
9/23/2022
9/24/2022
9/26/2022
9/30/2022
10/4/2022
10/5/2022
10/7/2022
10/11/2022
10/12/2022
10/13/2022
10/14/2022
10/16/2022
10/18/2022
10/22/2022
10/25/2022
10/29/2022
10/31/2022
11/2/2022
11/3/2022
11/6/2022
11/9/2022
11/10/2022
11/13/2022
11/17/2022
11/18/2022
11/21/2022
11/25/2022
11/29/2022
11/30/2022
12/2/2022
12/3/2022
12/5/2022
12/6/2022
12/7/2022
12/8/2022
12/10/2022
12/14/2022
12/15/2022
12/16/2022
12/20/2022
12/21/2022
12/23/2022
12/24/2022
12/27/2022
12/28/2022
12/30/2022
1/2/2023
1/3/2023
1/4/2023
1/8/2023
1/10/2023
1/12/2023
1/13/2023
1/16/2023
1/18/2023
1/20/2023
1/23/2023
1/27/2023
1/30/2023
2/3/2023
2/7/2023
2/9/2023
2/13/2023
2/15/2023
2/19/2023
2/20/2023
2/23/2023
2/27/2023
3/1/2023
3/5/2023
3/9/2023
3/11/2023
3/15/2023
3/17/2023
3/20/2023
3/22/2023
3/23/2023
3/25/2023
3/29/2023
3/31/2023
4/4/2023
4/5/2023
4/6/2023
4/7/2023
4/9/2023
4/12/2023
4/13/2023
4/14/2023
4/18/2023
4/20/2023
4/23/2023
4/26/2023
4/28/2023
5/2/2023
5/5/2023
5/6/2023
5/9/2023
5/12/2023
5/16/2023
5/17/2023
5/19/2023
5/21/2023
5/24/2023
5/25/2023
5/29/2023
6/2/2023
6/3/2023
6/7/2023
6/10/2023
6/12/2023
6/14/2023
6/18/2023
6/22/2023
6/26/2023
6/28/2023
6/30/2023
7/2/2023
7/3/2023
Solved! Go to Solution.
Try this:
Measure Count =
VAR __Table = ADDCOLUMNS('Events',"__YearMonth",YEAR([Event Date])*100+MONTH([Event Date]))
VAR __Table1 = GROUPBY(__Table,[__YearMonth],"__Count",COUNTX(CURRENTGROUP(),[Event Date]))
VAR __Max = MAXX(__Table1,[__Count])
RETURN
__Max
@steve_geist My pleasure. "better DAX'ing direction" is definitely subjective. I can't say that my views on things like variables, CALCULATE, time "intelligence" functions and the like aren't probaby controversial. They most certainly are. It's just how I write my DAX that allows me to solve issues quickly, efficiently and consistently. So, I make no claims of best practice here, it's just what works for me.
Thanks @Greg_Deckler , tried that DAX against the samples data above and it yielded 202k as the result...not sure if I enetred code wrong? Putting the data into a matrix visual shows the correct results is 18... ANy suggestions? Thanks again for insights!
@steve_geist In the format pane, change the Data Lable Display Units to None. You will get 202307.
@steve_geist - Perhaps try something like this:
Measure =
VAR __Table = ADDCOLUMNS('Table',"__YearMonth",YEAR([Date])*100+MONTH([Date]))
VAR __Table1 = SUMMARIZE(__Table,[__YearMonth],"__Count",COUNTROWS(__Table))
VAR __Max = MAXX(__Table1,[__Count])
RETURN
MAXX(FILTER(__Table1,[__Count]=__Max),[__YearMonth])
Thanks (again) @Greg_Deckler , but that DAX still yields a value of 202,307 instead of 18 which is the known correct value from the matrix visual.
Try this:
Measure Count =
VAR __Table = ADDCOLUMNS('Events',"__YearMonth",YEAR([Event Date])*100+MONTH([Event Date]))
VAR __Table1 = GROUPBY(__Table,[__YearMonth],"__Count",COUNTX(CURRENTGROUP(),[Event Date]))
VAR __Max = MAXX(__Table1,[__Count])
RETURN
__Max
Thanks @Greg_Deckler , that works perfectly!
May I ask if my understanding of how the measure works is correct?
...I think i am misunderstanding a few points though...
Sorry if questions aren;t clear, and no worries if you dont have time to answer, was just trying t understand how/why it works but regardless...IT WORKS!!!! Thanks you!
@steve_geist First, there are 50 ways to do everything in DAX. So, basically I wanted to make sure that I arrived at a unique year and month for grouping purposes. One way of doing this is to take the year * 100 and add the month so that you get things like:
202001
202002
202003
202101
etc.
Easy way to get a "unique" key basically. Now, you could potentially attempt to use CALCULATE and MAX and combine the two table variables within the CALCULATE statement. Might work. However, that's not how I tend to write my DAX at least initially. I find it far easier to write the DAX initially and troubleshoot using table variables and such. I wrote an entire article/rant about it. CALCUHATE - Why I Don't Use DAX's CALCULATE Functi... - Microsoft Power BI Community. So, when you use table variables, you can't use CALCULATE so you have to use the iterator functions (MAXX, SUMXX, etc.)
@Greg_Deckler , THANKS! I just went down the rabbit hole reading your articles! I myself am in this phase: "[Once you start to use variables]" you tend to graduate to using table variables. I've been creating a lot of VARiables to simplify my CALCULATE operations but am definitely still running into all the issues you describe...looks like I need get more comfortable with virtual tables and the X, SUMMARIZE, and GROUPBY functions.
Long story short, thanks for helping with the DAX for the measure I needed, but REALLY thanks for the nudge in a better DAX'ing direction 🙂
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |