Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) ) )
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |