Forum Discussion
Compare aggregated values and select max
I need help to summarize the data for a category per user and count the results filtered by max value for each user
For ex: In the below image, user - "SL-08" has an entry for category - H and 2 entries for category - F. So, in a give period, such as a week of 5/24, this user should be counted towards category - F (as shown in summarized data). If there's a tie, Category F takes precedence.
I need help with dax measure, there are other related tables and filters i need to apply using them to the data.
| Id | User | Category Id | Wk | Date | Category Name |
| 165 | SL-08 | 1 | 5/24/2020 | 5/26/2020 0:00 | H |
| 165 | SL-08 | 2 | 5/24/2020 | 5/28/2020 0:00 | F |
| 165 | SL-08 | 2 | 5/24/2020 | 5/29/2020 0:00 | F |
| 165 | SL-09 | 2 | 5/24/2020 | 5/27/2020 0:00 | F |
| 164 | SL-AE01 | 1 | 5/24/2020 | 5/26/2020 0:00 | H |
| 164 | SL-AE01 | 2 | 5/24/2020 | 5/27/2020 0:00 | F |
| 164 | SL-AE02 | 1 | 5/24/2020 | 5/26/2020 0:00 | H |
| 164 | SL-AE06 | 1 | 5/24/2020 | 5/26/2020 0:00 | H |
| 164 | SL-AE06 | 1 | 5/24/2020 | 5/27/2020 0:00 | H |
| 164 | SL-AE06 | 2 | 5/24/2020 | 5/26/2020 0:00 | F |
| 165 | SL-AE08 | 2 | 5/24/2020 | 5/27/2020 0:00 | F |
| 165 | SL-AE08 | 2 | 5/24/2020 | 5/26/2020 0:00 | F |
| 165 | SL-AE08 | 2 | 5/24/2020 | 5/27/2020 0:00 | F |
Thank You!!
7 Replies
- Greg_Deckler
Community Champion
Anonymous - This looks like a use case for Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Although simpler in your case, get the MAX of Date, use that to lookup the Category Name corresponding to that Date.
Greg_Deckler . Thanks, I've tried a similar approach before and it doesn't seem to get the output i need, especially for users that have multiple categories in the select period. it selects both the categories for the user in this case.
VAR __summarytable = // ADDCOLUMNS ( SUMMARIZE ( Test2 , Test2[User] , Test2[Category Id] , "MaxxCount",CALCULATE ( COUNTROWS(Test2)) , "TWCount",CALCULATE ( COUNTROWS(Test2), FILTER(Test2,Test2[Category Id] = 1)) , "AFCount",CALCULATE ( COUNTROWS(Test2), FILTER(Test2,Test2[Category Id] = 2)) , "VisitCount",CALCULATE ( COUNTROWS(Test2), FILTER(Test2,Test2[Category Id] = 4)) ) VAR __Max = MAXX(__summarytable,[MaxxCount]) RETURN COUNTROWS(FILTER(__summarytable,[MaxxCount] = __Max || [AFCount] >= [TWCount]))- Greg_Deckler
Anonymous - Can you post sample data as text and expected output? So much easier to troubleshoot if I can recreate the problem locally.
- amitchandak
Super User
Anonymous , Try like
lastnonblankvalue(Table[Date], max(Table[Categort]))
Week calendar - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
WOW -https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123 
V-lianl-msftCommunity Support
Hi Anonymous ,
Based on your description, F has a priority, so you can first create a calculated column like this:
rank = VAR count_catefory = CALCULATE ( COUNT ( 'Table (2)'[Category Id] ), ALLEXCEPT ( 'Table (2)', 'Table (2)'[User], 'Table (2)'[Wk], 'Table (2)'[Category Name] ) ) VAR precedence = IF ( 'Table (2)'[Category Name] = "F", count_catefory + 0.5, count_catefory ) RETURN precedenceThen create a calculated column to get the category name of each user.
category_test = VAR max_ = CALCULATE ( MAX ( 'Table (2)'[rank] ), ALLEXCEPT ( 'Table (2)', 'Table (2)'[User] ) ) RETURN CALCULATE ( FIRSTNONBLANK ( 'Table (2)'[Category Name], 1 ), FILTER ( 'Table (2)', 'Table (2)'[rank] = max_ ) )The third graph cannot be achieved in power Bi for the time being
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi V-lianl-msft. Thank you! Area graph shown in the final result is what i really need and i would like to use measures to make it dynamic because it needs to return data for period selected month/week etc, actual data set is over 8M+ rows
Also, I've added the "week" and "category Name" columns for ease. They both have different tables
