Forum Discussion

Chanleakna123's avatar
Chanleakna123
Icon for Post Prodigy rankPost Prodigy
8 years ago

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

 

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. 

 

 

Data Modeling

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      Chanleakna123
      Icon for Post Prodigy rankPost Prodigy
      8 years ago

       

      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's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        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