Forum Discussion

vincentakatoh's avatar
vincentakatoh
Helper 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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi vincentakatoh,

     

    Would you like to add a calculated column in the table? I tested a formula, similar with the one below, as a calculated column in a table with 3 millions rows. It's finished in less than one minute. Maybe you can have a try.

    MaxAttempts =
CALCULATE (
    MAX ( 'Table1'[attempts] ),
    FILTER (
        'Table1',
        'Table1'[Student] = EARLIER ( 'Table1'[Student] )
            && Table1[subject] = EARLIER ( 'Table1'[subject] )
    )
)

    Best Regards!

    Dale

TomMartens's avatar
TomMartens
Super 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
Helper 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
    Super 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)