Log In
Register
Home
July 23, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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?
Trusted Members
Moderators
November 1, 2018
Offline
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())
July 23, 2019
Offline
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.
1 Guest(s)
