Forum Discussion

vincentakatoh's avatar
vincentakatoh
Icon for Helper IV rankHelper IV
9 years ago
Solved

Query Editor: Custom column, List.Max, formula

 Hi,    Trying to add custom column (AttemptsMax) to show the maximum attempts per student and per subject.   Need help on the custom column formula. Guess i should be using the List.Max function...
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

Hmm,

 

I do not actually understand why you want to avoid the GroupBy custom column funtion.

 

If I want to add an aggregated value e.g. MAX(Attempts) to a Group (Subset) of values without loosing the detail values I do the following:

 

 

the column "expansion" is just a "dummy" column that is not further used.

After this I just have to expand the table and select the Value column.

 

Maybe this helps even it involves the GroupBy Transform

vincentakatoh's avatar
vincentakatoh
Icon for Helper IV rankHelper IV
9 years ago

Hi TomMartens, 

 

Thanks. Used the "Groupby" but problem is Query Editor hangs or fails when i run using my actual data (>500k rows, 70mb, per day). 

 

As such, I'm hoping I can try to add a custom column with formula instead. 

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    Okay I understand the problem.

     

    So here we have to create very efficiently "a list" from where List.Max has to determine the MAX value. This has to be done for each row again and again ...

     

    Have you tried a somewhat brute force method (or does Power BI hangs here as well)

    • create a table with the grouped (MAX) value and later on (in SQL Server this would be CTE)
    • merge the aggregated table to the base table (in SQL Server this would be Join the BaseTable with the CTE)