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

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)