Kludged Training Check with If Then, Power Query

[jamiers]

Hi All,

I am working on a training table to make sure students complete all of their classes before being promoted into a position. That being said, I have individual columns that that come back with a √ in it if have completed a class. I am running an if/then check to determine if all of the checks are in place, before providing a results.

Basically, the first check is to determine if the position is blank.

• If it is blank and all the checks AREN'T there, then it should return a result of DONT CARE.
• If it is blank and all the checks ARE THERE, then it should return a result of ALERT.
• If it IS NOT BLANK and all the checks ARE THERE, then it should return a SV-Yes.
• If it IS NOT BLANK and then all the checks AREN'T THERE, then it should return a SV-No

This has been working pretty well until I merged a Level 1 position with the Level 2 position -- all training on the same page and check for two positions on the same page. Basically Level 1 = Worker Bee, Level 2 = Supervisor of the Worker Bee.

What I want to do is change the Comply-SV to first check if the worker's position (the field is [GAP-SH.GAP SH/]) isblank or listed as "SA" in the part of the code that starts off with Comply-SV = if(ISBLANK('!MergedTraining'[GAP-SH.GAP SH/]), I have been looking at this and my brain is just fried. Hoping for some gentle assistance. Thanks... Jamie

Comply-SV = if(ISBLANK('!MergedTraining'[GAP-SH.GAP SH/]),if ('!MergedTraining'[DCSOVRW.DCS Ovr] = "√" && '!MergedTraining'[DCSEIW.EIW] = "√" && '!MergedTraining'[SHFUNDv2.SH Fnd v2 or Sh Fnd 2017] = "√"  && '!MergedTraining'[DEPFUND.Dep Fnd Away or Dep Fnd] = "√"  && '!MergedTraining'[DCSMISCDOVR.Mis Cd Ovr] = "√" && '!MergedTraining'[DCSSHSV.SH SV or SH MGT 2017] = "√" && '!MergedTraining'[BASICFOOD.Bas Food Safty] = "√" && '!MergedTraining'[PFA.PFA] = "√" && '!MergedTraining'[DCSSHRES.SRT in a Sh] = "√" && '!MergedTraining'[DCSSDW.SDW] = "√",  "ALERT", "Dont Care"), if ('!MergedTraining'[DCSOVRW.DCS Ovr] = "√" && '!MergedTraining'[DCSEIW.EIW] = "√" && '!MergedTraining'[SHFUNDv2.SH Fnd v2 or Sh Fnd 2017] = "√"  && '!MergedTraining'[DEPFUND.Dep Fnd Away or Dep Fnd] = "√"  && '!MergedTraining'[DCSMISCDOVR.Mis Cd Ovr] = "√" && '!MergedTraining'[DCSSHSV.SH SV or SH MGT 2017] = "√" && '!MergedTraining'[BASICFOOD.Bas Food Safty] = "√" && '!MergedTraining'[PFA.PFA] = "√" && '!MergedTraining'[DCSSHRES.SRT in a Sh] = "√" && '!MergedTraining'[DCSSDW.SDW] = "√",  "SV-Yes", "SV-No"))

 

[jorismoerings]

Hi,

Try starting your DAX formula with this:

Comply-SV = if(ISBLANK('!MergedTraining'[GAP-SH.GAP SH/])|| LEFT('!MergedTraining'[GAP-SH.GAP SH/],2)="SA",if ('!MergedTraining'[DCSOVRW.DCS Ovr] = "√" && '!MergedTraining'[DCSEIW.EIW] = "√" && '!MergedTraining'[SHFUNDv2.SH Fnd v2 or Sh Fnd 2017] = "√"  && '!MergedTraining'[DEPFUND.Dep Fnd Away or Dep Fnd] = "√"  && '!MergedTraining'[DCSMISCDOVR.Mis Cd Ovr] = "√" && '!MergedTraining'[DCSSHSV.SH SV or SH MGT 2017] = "√" && '!MergedTraining'[BASICFOOD.Bas Food Safty] = "√" && '!MergedTraining'[PFA.PFA] = "√" && '!MergedTraining'[DCSSHRES.SRT in a Sh] = "√" && '!MergedTraining'[DCSSDW.SDW] = "√",  "ALERT", "Dont Care"), if ('!MergedTraining'[DCSOVRW.DCS Ovr] = "√" && '!MergedTraining'[DCSEIW.EIW] = "√" && '!MergedTraining'[SHFUNDv2.SH Fnd v2 or Sh Fnd 2017] = "√"  && '!MergedTraining'[DEPFUND.Dep Fnd Away or Dep Fnd] = "√"  && '!MergedTraining'[DCSMISCDOVR.Mis Cd Ovr] = "√" && '!MergedTraining'[DCSSHSV.SH SV or SH MGT 2017] = "√" && '!MergedTraining'[BASICFOOD.Bas Food Safty] = "√" && '!MergedTraining'[PFA.PFA] = "√" && '!MergedTraining'[DCSSHRES.SRT in a Sh] = "√" && '!MergedTraining'[DCSSDW.SDW] = "√",  "SV-Yes", "SV-No"))

 

[jamiers]

OK... I think that did it. Thank you @jorismoerings. Can you explain what you did so I understand it?

Jamie

 

[jorismoerings]

Hi,

Of course. Within DAX the double ampersand (&&) means AND, the double Pipe (||) means OR.
So basically i added the OR part to the first check within the IF