Compare aggregated values and select max

[Anonymous]

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!!

[V-lianl-msft]

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
    precedence

Then 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

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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

[amitchandak]

@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

[Greg_Deckler]

@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.

[Anonymous]

@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.

[Anonymous]

Greg, V-lianl-msft,

Apologies, i created a duplicate post. 

@Greg_Deckler  - please take a look at this thread. I've tried to explain the scenario and added sample data, with expected result
https://community.powerbi.com/t5/Desktop/Compare-counts-with-in-group-rank-and-count-the-top-values/...