topic Re: Repartition by max activity in DAX Commands and Tips

Repartition by max activity

MichiyoTora — Thu, 12 Dec 2024 10:52:40 GMT

Hi !
I've data like this :

My goal is to have a pie shart to show the repartition of the members by their most used activity like this :

I tried a lot of things to do this but I didn't succeed.
The difficulty is to show only the type of activity the most used by user (the total have to be the number of users, not take them in count several times in differents types), and it change with the selected date range in visual slicer, so a calculated column is not enought for this problem.

Can you help me please ?
Thanks !

Re: Repartition by max activity

johnt75 — Thu, 12 Dec 2024 11:31:18 GMT

You can try

Count members with main activity type = VAR CurrentType = SELECTEDVALUE ( activity[type] ) VAR BaseTable = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( activity, activity[account], activity[type] ), REMOVEFILTERS ( activity[type] ) ), "@sum", CALCULATE ( SUM ( activity[number] ) ) ) VAR PartitionedTable = INDEX ( 1, BaseTable, ORDERBY ( [@sum], DESC ), PARTITIONBY ( activity[account] ) ) VAR Types = GROUPBY ( PartitionedTable, activity[type], "@users", SUMX ( CURRENTGROUP (), 1 ) ) VAR Result = SUMX ( FILTER ( Types, activity[type] = CurrentType ), [@users] ) RETURN Result

Re: Repartition by max activity

MichiyoTora — Thu, 12 Dec 2024 12:41:32 GMT

It seems not working, members still in each type of activity they use, not only the principal one ...

Re: Repartition by max activity

mark_endicott — Thu, 12 Dec 2024 13:27:49 GMT

MichiyoTora - it would be useful to have some sample data to test but I would structure this like so:

Create a measure called Number of Activity:

SUM( activity[number] )

Then reference it in an additional measure:

VAR RankedActivities = ADDCOLUMNS ( SUMMARIZE ( activity, activity[account], activity[type] ), "ActivityRank", RANK ( DENSE, ALLSELECTED ( activity[type] ), ORDERBY([Number of Activities], DESC), , PARTITIONBY(activity[account]) ) ) VAR TopActivity = FILTER ( RankedActivities, [ActivityRank] = 1 ) RETURN CALCULATE ( [Number of Activities], TREATAS ( SELECTCOLUMNS ( TopActivity, "account", activity[account] ), TD_members[account] ) )

There may be an issue with Ranking in this way inside an add columns, but if you can provide some sample data it would be easier to resolve.

Of course if this works, please accept as the solution. It helps with visibility for others with the same challenge!

Re: Repartition by max activity

MichiyoTora — Thu, 12 Dec 2024 14:14:41 GMT

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

Re: Repartition by max activity

Anonymous — Fri, 13 Dec 2024 08:55:45 GMT

Thanks for the reply from mark_endicott and johnt75 , please allow me to provide another insight:
Hi MichiyoTora ,

I can't open your PBIX, I created the sample data, you can try to create a measure using a virtual table to get the maximum count and then put it in a filter set to 1 to achieve.

Here are the steps you can follow：

1. Create measure.

Flag = var _mindate=MINX(ALLSELECTED('Table'),[date]) var _maxdate=MAXX(ALLSELECTED('Table'),[date]) var _table= FILTER(ALLSELECTED('Table'),[date]>=_mindate&&[date]<=_maxdate) var _table2= ADDCOLUMNS( _table,"Count",COUNTX(FILTER(_table,[type]=EARLIER([type])),[account])) var _test= CONCATENATEX( FILTER(_table2,[Count]=MAXX(_table2,[Count])),[type],"-") return IF( CONTAINSSTRING( _test,MAX('Table'[type]))=TRUE(),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

Re: Repartition by max activity

MichiyoTora — Fri, 13 Dec 2024 09:10:58 GMT

Hi !
Thank's for your help. It's an approach I've already try.
If I apply your proposition, it doesn't work because it shows only 1 type of activity (the most representative)
In your sample of data for example, if the account 3 use "push" more than other activities types, we should have a pie chart with 7 team and 1 push, but we still have 8 team.

Re: Repartition by max activity

johnt75 — Fri, 13 Dec 2024 10:26:17 GMT

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

Re: Repartition by max activity

MichiyoTora — Fri, 13 Dec 2024 10:51:55 GMT

Yes ! It perfectly works ! (A little bit long with a big number of data, but not a problem)
Thank's a lot !

Re: Repartition by max activity

mark_endicott — Fri, 13 Dec 2024 10:56:17 GMT

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.