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,
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.
Solved! Go to Solution.
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
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
gOOD Day, when i apply this in power Query its giving me an expression error the Nale CALCULATE wasnt recognised
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
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.
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).
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
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.
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)
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |