Forum Discussion
Helper ICombining Data from multiple rows/filters in DAX
Hi all
I have the following tables:
On my report page I need to report on the 3 subjects individually by term for % of students with A grades, % with B or better, etc, and I got that bit fine. But I also need to report on a combined metric, where we get the % of students with all 3 at A, all 3 at B or better, etc.
I've taken over this report from someone else and they got round the problem by creating loads and loads of extra calculated columns on the student table to look up the result for a specific subject for a specific term, and then a final set that gives 1 or 0 for whether they got all 3 at each minimum grade, but as we scale the reports up this is causing issues because everything is hard coded and it's not reactive to filters on the assessment table.
I'd like to write something in DAX that will give me the same sort of thing, with a single DAX measure by grade, so that I can use that with dynamic term filters (or filters on other assessment table columns not listed here). But because I'm trying to look up multiple different values in multiple rows on the assessment table to make it work I'm stuck on how to even start writing the DAX. Had a google around but couldn't find anything really relevant (at least not in a way I understood)
Can anyone point me in the right direction? Once I know where to start I should be OK but I've tried a few things and keep bashing my head against a wall of "nope, that doesn't work how you need it to".
Thanks
Thanks for the reply from lbendlin @SuperUser/@NormalUser, please allow me to provide another insight.
Hi crispybits77 ,
Here are the steps you can follow：
1. Create calculated slicer table.
Slicer_Table = DISTINCT('ASSESSMENT table'[Grade])2. Create measure.
individually by term for % of students with select grades = var _select=SELECTEDVALUE('Slicer_Table'[Grade]) var _count= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]), FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm])&&'ASSESSMENT table'[Grade]=_select)) var _countgroup= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]), FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm]))) var _divide= DIVIDE( _count,_countgroup) return IF( _divide=BLANK(),0,_divide)% 3 student = var _select=SELECTEDVALUE('Slicer_Table'[Grade]) var _countselect= COUNTX( FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Student ID]=MAX('ASSESSMENT table'[Student ID])&&'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm])&&'ASSESSMENT table'[Grade]=_select),[Grade]) RETURN _countselect% of students with all 3 at select = var _select=SELECTEDVALUE('Slicer_Table'[Grade]) var _countgroup= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]), FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm]))) var _count= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]),FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm])&&[% 3 student]=3)) var _divide= DIVIDE( _count,_countgroup) return IF( _divide=BLANK(),0,_divide)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Thanks for the reply from lbendlin @SuperUser/@NormalUser, please allow me to provide another insight.
Hi crispybits77 ,
Here are the steps you can follow：
1. Create calculated slicer table.
Slicer_Table = DISTINCT('ASSESSMENT table'[Grade])2. Create measure.
individually by term for % of students with select grades = var _select=SELECTEDVALUE('Slicer_Table'[Grade]) var _count= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]), FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm])&&'ASSESSMENT table'[Grade]=_select)) var _countgroup= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]), FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm]))) var _divide= DIVIDE( _count,_countgroup) return IF( _divide=BLANK(),0,_divide)% 3 student = var _select=SELECTEDVALUE('Slicer_Table'[Grade]) var _countselect= COUNTX( FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Student ID]=MAX('ASSESSMENT table'[Student ID])&&'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm])&&'ASSESSMENT table'[Grade]=_select),[Grade]) RETURN _countselect% of students with all 3 at select = var _select=SELECTEDVALUE('Slicer_Table'[Grade]) var _countgroup= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]), FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm]))) var _count= CALCULATE(DISTINCTCOUNT('ASSESSMENT table'[Student ID]),FILTER(ALL('ASSESSMENT table'),'ASSESSMENT table'[Tearm]=MAX('ASSESSMENT table'[Tearm])&&[% 3 student]=3)) var _divide= DIVIDE( _count,_countgroup) return IF( _divide=BLANK(),0,_divide)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
crispybits77Thank you - just what I needed (looking at my efforts I was so close - just missing a couple of vital little bits...)
