Forum Discussion
Repartition by max activity
This works if you use columns from the dimension table rather than the activity table
Count members with main activity type = VAR CurrentType = SELECTEDVALUE ( Activities[type] ) VAR BaseTable = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( activity, Users[account], Activities[type] ), REMOVEFILTERS ( Activities[type] ) ), "@sum", CALCULATE (COUNTROWS( activity ) ) ) VAR PartitionedTable = INDEX ( 1, BaseTable, ORDERBY ( [@sum], DESC ), PARTITIONBY ( Users[account] ) ) VAR Types = GROUPBY ( PartitionedTable, Activities[type], "@users", SUMX ( CURRENTGROUP (), 1 ) ) VAR Result = SUMX ( FILTER ( Types, Activities[type] = CurrentType ), [@users] ) RETURN Result
Thank's a lot for your time and your help.
Power BI found a problem with the partitionby part of your measure. I don't know well enough this option to correct it.
Here is a sample of my acticity data, you'll be able to create dimensions tables with values function : https://docs.google.com/spreadsheets/d/1RUHtEUY8Ywsi4vPLvJzk-vh6QSRg6HyOWSWz-PDfFMg/edit?usp=sharing
Super UserThis works if you use columns from the dimension table rather than the activity table
Count members with main activity type =
VAR CurrentType =
SELECTEDVALUE ( Activities[type] )
VAR BaseTable =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( activity, Users[account], Activities[type] ),
REMOVEFILTERS ( Activities[type] )
),
"@sum", CALCULATE (COUNTROWS( activity ) )
)
VAR PartitionedTable =
INDEX (
1,
BaseTable,
ORDERBY ( [@sum], DESC ),
PARTITIONBY ( Users[account] )
)
VAR Types =
GROUPBY (
PartitionedTable,
Activities[type],
"@users", SUMX ( CURRENTGROUP (), 1 )
)
VAR Result =
SUMX ( FILTER ( Types, Activities[type] = CurrentType ), [@users] )
RETURN
Result
Yes ! It perfectly works ! (A little bit long with a big number of data, but not a problem)
Thank's a lot !- mark_endicott
MichiyoTora - Glad johnt75 's approach worked, I misread your requirement so even if I fixed my DAX it would've given you a completely incorrect answer! Apologies.
