!!!! Stuck with Condition formatting with Text + together

[Chanleakna123]

All ,

1st : I have relationship between Staff and Training Subject . 

2nd , My Training Report also cover with Training Subject in 2nd Photo 

3rd , I have Training Subject with Non BIG Program and BIG Program . 

MY purpose here is : 

If Employee Study BIG Program :

for ex: STEP 1 + STEP 2 + STEP 3 = Graduated , but if the Employee Study only  STEP 1 = 2 More STEPs Subject to go , 

Can anyone here tell me how to succeed this ? I tried multiple condition but it doesn't work , And i'm stuck , while i don't know which way and which text should i go. 

Your Guide and screen shot on each step is really in big help on my report. 

Thanks you so much. 

[v-qiuyu-msft]

Hi @Chanleakna123,

Please share some dummy data of tables Staff, Training Subject and Training Report to clarify your requirement "

If Employee Study BIG Program :

for ex: STEP 1 + STEP 2 + STEP 3 = Graduated , but if the Employee Study only  STEP 1 = 2 More STEPs Subject to go "?

Which do you mean about "STEP 1 + STEP 2 + STEP 3"?

What the desired result based on this sample data? 

Best Regards,
Qiuyun Yu 

[Chanleakna123]

hi @v-qiuyu-msft kindly find above attached for the table i used. 

STEP 1 + STEP 2 + STEP 3 are the course program.

what we want is to see is who will be graduated and who NON.  
In our internal company , for BIG Program , those who complete BIG program will be graduated. but we have exception , Few course programs , we need to study session STEP 1 + STEP 2 + STEP 3 = to be graduated. few course programs study AYBS 1 + AYBS 2 = Graduated . LEAP 1 + LEAP 2 = graduated. If they study onlly LEAP 1 , will be NON graduated.

so , we nce we accomplish this we will visualize , which course our employee had been graduated ? 

which course they're still pending for graduated ? 

[v-qiuyu-msft]

Hi @Chanleakna123,

I will share some logic with small data for you go deeper: 

For your Training Subject table, you need to create a new group for Course Title, see: https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

Then create a calculated column to count session which needed to complete in each group:

Count session per group = CALCULATE(COUNT('Training Subject'[Course Title]),ALLEXCEPT('Training Subject','Training Subject'[Course Title (groups)]))

In table Training Report table, create a calculated column to return related group:

Group = RELATED('Training Subject'[Course Title (groups)])

Then create another calculated column to decide if the employee has completed the course:

Total session per emp = IF(CALCULATE(COUNT('Training Report'[Course Title]),FILTER('Training Report','Training Report'[Staff ID]=EARLIER('Training Report'[Staff ID]) &&'Training Report'[Group]=EARLIER('Training Report'[Group])))=RELATED('Training Subject'[Count session per group]),"gra","no gra")

You can take a look at the pbix file. 

Best Regards,
Qiuyun Yu