Conditional variables

[Anonymous]

Hi everyone, 

I ran into a problem and I am not sure how to fix it in PowerBI. Here's the context:

Every employee in companyA is offered an educational course in Dutch and English, the employee picks the preferred language of choice and completes the course. However, when an employee picks the English course they, of course, don't need to fulfil the exact same course in another language. But because the course was still offered, the course shows up as expired. These double values contaminate the final dashboards. 

	Course1 code English	Course1 status	Course1 code Dutch	Course1 status
John	R4 EN ID:C1	Completed	R4 NL ID:C1	Expired
Michael	R4 EN ID:C1	Expired	R4 NL ID:C1	Completed
Anne	R4 EN ID:C1	Expired	R4 NL ID:C1	Completed
Rose	R4 EN ID:C1	Expired	R4 NL ID:C1	Expired

Is there any way to mitigate John's Dutch course1 status because he completed the English course1? I am at a complete loss on how to do this with DAX formulas. 

Note, sometimes an employee doesn't complete any courses, so it's still important to count the expired status in certain circumstances. 

Thank you in advance!

[ryan_mayu]

@Anonymous 

Is the table you showed a visual or raw data? and could you please show the expected results?

[Anonymous]

Hi Ryan, 

Thank you for responding. The final results should look something like this: 

Users who completed course 1 = 3 (John, Michael, Anne)

Users who did not complete course 1 = 1 (Rose)

How it looks right now in my PowerBI is this:

Users who completed course 1 = 3 (John, Michael, Anne)

Users who did not complete course 1 = 4 (John, Michael, Anne, Rose)

[ryan_mayu · Accepted solution]

@Anonymous 

I am still not sure the table you showed previously was a table in your powerbi or had measure in it.

I assumed it was a table.

You can create a column.

Final status = if('Sheet25'[Course1 status_1]="Completed","Completed",'Sheet25'[Course1 status])

Or you can create two measures

compolte = CALCULATE(COUNTROWS(Sheet25),FILTER(Sheet25,Sheet25[Course1 status]="Completed" || 'Sheet25'[Course1 status_1]="Completed"))

not completed = CALCULATE(COUNTROWS(Sheet25),FILTER(Sheet25,Sheet25[Course1 status]="Expired" && 'Sheet25'[Course1 status_1]="Expired"))

Hope this is helpful.

[MFelix]

Hi @Anonymous ,

Not really  sure if this solution includes all the options but based on the information you present why don't you make an additional column that compares both status:

if ([Course1 status]=[Course1 status.1]) = true then [Course1 status] else List.Min({[Course1 status],[Course1 status.1]})

Result is below:

Now use this new column to calculate your desired KPI's.