Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]))