Good afternoon
Try as I may, I am unable to solve this issue myself and therefore appeal to your expertise once again!
I have a list of candidate results from various unit exams, I am trying to link the results of two units together to give an overall qualification result.
A little background that may be useful…
- The data I am using is exported from our training management system, hence why the data is presented in rows – 1 per delegate per exam / course
- Delegates must pass both Unit exams to achieve a qualification Pass
I have popped a standard Excel formula for this on the spreadsheet (not in DAX) to demonstrate what I am trying to achieve.
I also have an issue when trying to use CONCANTENATEX with the unfiltered pivot table. The pivot table lists each delegate but the CONCANTENATEX formula returns a comma for each row with the same delegate name, where the exam result cell is blank. I have tried to filter out the blanks, with no success.
Any guidance you can give would be greatly appreciated.
Kind regards
You can filter out the blanks using something like:
=CONCATENATEX(FILTER(values(Table1[NG1 Mark]),Table1[NG1 Mark]<>BLANK()),Table1[NG1 Mark],", ")
Regarding your result formula - is it looking at the right columns?
I think you want something like this:
=IF(COUNTROWS(filter(Table1,Table1[Course Ref]="NG1 Exam"&&Table1[Result]="PASS"))+COUNTROWS(filter(Table1,Table1[Course Ref]="NG2 Exam"&&Table1[Result]="PASS"))=2,"PASS",blank())
Wow - thanks very much Velouria - that is spot on!
Both formulae work perfectly. 
I really appreciate your assistance with this - this forum is such a fab place to get in touch with Excel Masters.
Thank you so much again Velouria.
You're welcome. 🙂
I'm no master though - still mostly making it up as I go along where DAX is concerned!
