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 v-jiascu-msft, 

 

Awesome. Works in <1min on actual data (280mb). 

 

Nonetheless, can advise how to add the equivalent using Query Editor "Custom Column"(M language), instead of "Calculated Column"(DAX). Reason being, eventually will need add more columns and get visuals such as pareto. 

 

Really appreciate your reply. Spent days on w/o success. 

vincentakatoh's avatar
vincentakatoh
Helper IV
9 years ago

Hi v-jiascu-msft,

 

Fyi, tried using the M equivalent (Advanced Editor) in Query Editor, but had same issue (using Group-by) as PBI hangs when more data is loaded. 

 

Truly appreciate your idea for using Calculated Column. For this specific purpose, Calculated Column (DAX) is more resource efficient than Custom Column/Advanced Editor (M).