Forum Discussion
Compare aggregated values and select max
Community ChampionAnonymous - 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.
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/m-p/1291186/highlight/true