Forum Discussion
Helper IVcovert DAX to M language
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] )
)
)
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.
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"
8 Replies
- Greg_Deckler
Community Champion
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.
vincentakatohHi 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
