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. Also wanted to avoid using the "Group by" feature.

 

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

8 Replies

  • 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

  • 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)