Forum Discussion
Helper IVQuery Editor: Alternative to "Group By"
Hi,
Is there any alternative to the "Group by" function. Below is my sample data:
1) Objective is to add column, "Final Test Result" (in blue).
2) I can add the column, Final Test Result, using the "Group by" function. But it works only small sample data. My actual data has 18 columns and >500k rows (per csv file). After adding 4 csv files, the Query editor fails. Think calculation is too much for Power BI.
3) As such, will like to know if there is any other alternative methold to add the column "Final Test Result"
4 Replies
v-qiuyu-msftCommunity Support
Hi vincentakatoh,
You can try to use DAX to return the expected column:
Final test result = var t=CALCULATE(MAX('Table2'[Attempts]),ALLEXCEPT(Table2,Table2[Student],'Table2'[subject]))
returnIF('Table2'[Attempts]=t, LOOKUPVALUE('Table2'[test result],'Table2'[Student],'Table2'[Student],'Table2'[subject],'Table2'[subject],'Table2'[Attempts],t),BLANK())
Best Regards,
Qiuyun Yu
vincentakatohhi v-qiuyu-msft,
Awesome.
Is there a way to add as a Column (m code?) instead of a Measure (dax)? Will need to use the column, FinalTestResult for other purpose.
RobertSlatteryResponsive Resident
You can just add a calculated column
output = Table.AddColumn(#"my table", "Final Test Result", each if [test result] = "Pass" or [First test result] = "Pass" then "Pass" else null)