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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
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). 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    gOOD Day, when i apply this in power Query its giving me an expression error the Nale CALCULATE wasnt recognised

    • TomMartens's avatar
      TomMartens
      Super User
      6 years ago

      Hey Anonymous ,

       

      you have to be aware that the accepted solution provided by v-jiascu-msft is based on DAX, for this reason this approach can not be used inside Power Query. Power Query is based on M (a functional programming language), and the data model is using DAX.

       

      Regards,

      Tom