covert DAX to M language

[vincentakatoh]

Hi, 

Need help to convert below DAX to M language.

Afterwhich, how do i add the M language code? In a) "Add Custom Column" or b) "Advanced Editor"?

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

[ImkeF · Accepted solution]

Sure: https://1drv.ms/u/s!Av_aAl3fXRbehasTVNp_izcVIvgFrA

With a couple of million rows, load might be faster with the "fast" approach (using Table.Group).

Memory-wise: Currently you could skip column "Attempts" and count the rows instead (List.Count instead of List.Max) - actually this might speed up load as well. But your sample data might give a too simplified view of your actual situation.

[Greg_Deckler]

My suspicion is that is going to be fairly difficult to convert to M language due to the fact that M essentially deals with things at a row level primarily. For the first part of that, you could add a custom column with a Table.Max expression perhaps. In any event, perhaps @ImkeF has some ideas for you.

[ImkeF]

You can do both 🙂

a) resembles more the DAX-logic but is slow:

= Table.AddColumn(Table1, "Slow", each 
List.Max(Table.SelectRows(Table1, (table1)=>
				table1[Student]=[Student] and 
				table1[Subject]=[Subject])
			  [Attempts])
	)

If you do this via the UI, you just have to paste the code from row 2-5 into the dialogue-field.

b) is fast and a bit more advanced:

= Table.Join(Table1, {"Student", "Subject"}, 
	     Table.Group(Table1, {"Student", "Subject"}, {{"Max", each List.Max(_[Attempts])}}), {"Student", "Subject"}
	     )

This row has to be entered into the advanced editor or the formula-bar in the query editor.

[vincentakatoh]

Hi @ImkeF, 

Tried both A) & B) but get error messages for both. Can u send me a sample pbix with both? Thanks. 

My current solution uses "Calculated Column" (DAX). Was hoping to use "Custom Column" (M), thinking that M will take less system resource than DAX. For this case, am I correct? Actual data is a few millions rows. As such, system resource is important. 

Student	Subject	Attempts	test result
111	Test_A	1	Fail1
111	Test_A	2	Fail2
111	Test_A	3	Fail3
111	Test_A	4	Fail4
111	Test_B	1	Fail1
111	Test_B	2	Fail2
111	Test_B	3	Pass3
222	Test_A	1	Fail1
222	Test_A	2	Fail2
222	Test_A	3	Fail3
222	Test_B	1	Pass1
333	Test_C	1	Fail1
333	Test_C	2	Pass2

[ImkeF · Accepted solution]

Sure: https://1drv.ms/u/s!Av_aAl3fXRbehasTVNp_izcVIvgFrA

With a couple of million rows, load might be faster with the "fast" approach (using Table.Group).

Memory-wise: Currently you could skip column "Attempts" and count the rows instead (List.Count instead of List.Max) - actually this might speed up load as well. But your sample data might give a too simplified view of your actual situation.

[vincentakatoh]

Hi @ImkeF,

I'm really a M-dummy. Please help!

Can advise how to add the "Fast" approach directly into the original table, Advance Editor? Or can you provide a sample (without creating seperate table "Fast"). Added below, but got an error message.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Final Test Results.xlsx"), null, true),
    #"Raw data_no results_Sheet" = Source{[Item="Raw data_no results",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Raw data_no results_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Student", Int64.Type}, {"Subject", type text}, {"Attempts", Int64.Type}, {"test result", type text}})
	#"Custom1" = Table.Join(Source, {"Student", "Subject"}, Table.Group(Source, {"Student", "Subject"}, {{"Max", each List.Max(_[Attempts])}}), {"Student", "Subject"}),
in
    #"Custom1"

Added the code but there is an error message.

IF a new table "Fast" is a must, can advise how?

[ImkeF · Accepted solution]

No worries! You have to reference the previous step (that was "Source" in my query and is #"Changed Type" in yours):

let
    Source = Excel.Workbook(File.Contents("C:\Users\Final Test Results.xlsx"), null, true),
    #"Raw data_no results_Sheet" = Source{[Item="Raw data_no results",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Raw data_no results_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Student", Int64.Type}, {"Subject", type text}, {"Attempts", Int64.Type}, {"test result", type text}})
    #"Custom1" = Table.Join(#"Changed Type", {"Student", "Subject"}, Table.Group(#"Changed Type", {"Student", "Subject"}, {{"Max", each List.Max(_[Attempts])}}), {"Student", "Subject"}),
in
    #"Custom1"

[vincentakatoh]

Hi @ImkeF,

Solved again. thanks for your patience. PowerBI, Power Support!

[vincentakatoh]

Hi @ImkeF and @Greg_Deckler,

Just wanted to feedback my observation. Conclusion is to use "Calculated Column" (DAX). 

With great help from @ImkeF, converted the DAX to M. Nonetheless, on actual data, the PBI hanged when more data is ended.  

As such, maybe for my specific purpose, Calculated Column is more resource efficient.