Search code examples
excelpowerpivotdax

PowerPivot/DAX measure with SQL 'WHERE IN'-type restriction on just one one value

I am reporting on course completions by site, using a very simple DataModel:

enter image description here

This gives me a PivotTable that looks the one shown right below:

enter image description here

A complication I'm dealing with is that I only want to report percentages for the Course1 module based on people with the job titles found in the Course1 Audience table shown to the left of the PivotTable. Currently I'm achieving this by hard-coding those job titles into my measure like so:

IF(HASONEVALUE(Completions[Module]),
    IF(VALUES(Completions[Module])="Course1",
        CALCULATE(COUNTA(Completions[Email]),
            OrgChart[Title]="Job Title 1"
            ||OrgChart[Title]="Job Title 2"
            ||OrgChart[Title]="Job Title 3"
            ||OrgChart[Title]="Job Title 4"
            ||OrgChart[Title]="Job Title 5")
         /CALCULATE(COUNTA(AllStaff[Email]),
            OrgChart[Title]="Job Title 1"
            ||OrgChart[Title]="Job Title 2"
            ||OrgChart[Title]="Job Title 3"
            ||OrgChart[Title]="Job Title 4"
            ||OrgChart[Title]="Job Title 5"))
    ,COUNTA(Completions[Email])/COUNTA(AllStaff[Email]))
,BLANK())

While that works just fine, it's rather unwieldy and I just hate hard-coding logic into a measure like that. I'd much rather have the measure suck those Titles for Course1 from the Table, so that end users can amend this list without having to understand how to write DAX measures.

Is there any way I can instead reference the "Course 1 Audience" Table in my measure instead of these bits?: 

OrgChart[Title]="Job Title 1"
||OrgChart[Title]="Job Title 2"
||OrgChart[Title]="Job Title 3"
||OrgChart[Title]="Job Title 4"
||OrgChart[Title]="Job Title 5"

Note that this only applies to Course1. The completion rates of all other courses should be calculated based on ALL staff, and NOT excluded to just those job titles in the Course 1 Audience table.

Solution

  • You can use the CONTAINS() function. This little snippet should work, though I don't have your dat so I can't test it out. Make sure your Course 1 Audience data is stored in your data model as well.

    FILTER(OrgChart,CONTAINS(VALUES([Course 1 Audience]),[Course 1 Audience],OrgChart[Title])