Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DavidGM
Helper I
Helper I

How to show status of the training completion by User Name

 

keyUserManagerBICDToolPODBTrarining Complete
1AlexShaneYYYYYY
2AngelaShaneYYYYYY
3AndrewShaneYYYYNN
4JamieChrisYYYNYN
5JosephChrisNNNNNN
6SmithKatherineY - PASSYY - PASSYYY
7BravoKatherineYYYYYY
8DeanLeeYYYYYY
9JohnLeeYYYYYY
10William LeeYYYYNN
         

 

Training Complete Excel formula= =IF(COUNTIF(D2:H2,"")>0,"N",IF(COUNTIF(D2:H2,"N")>0,"N","Y")) for all the rows in Training Complete.

 

Problem:  I like to show in PowerBI the status of the training completion by user name, report should show completion status of training complete and overall % complete. I like to filter on the Manager

 

Thanks in advance team.

1 ACCEPTED SOLUTION

It worked well for me! Thanks

 

View solution in original post

5 REPLIES 5
MahyarTF
Memorable Member
Memorable Member

Hi,

You could create the calculated columns as below :

- for Training Status :

Training Status Y/N = if(UPPER(left(trim(Sheet217[BI]),1)) & UPPER(left(trim(Sheet217[CD]),1)) & UPPER(left(trim(Sheet217[Tool]),1)) &
                      UPPER(left(trim(Sheet217[PO]),1)) & UPPER(left(trim(Sheet217[DB]),1)) = "YYYYY",
                      "Y",
                      "N"
                    )

- For Training Passing Ratio :

Training % =
Var BIStatus = if( UPPER(left(trim(Sheet217[BI]),1)) = "Y", 1, 0)
Var CDStatus = If( UPPER(left(trim(Sheet217[CD]),1)) = "Y", 1, 0)
Var ToolStatus = If( UPPER(left(trim(Sheet217[Tool]),1)) = "Y", 1, 0)
Var POStatus = If( UPPER(left(trim(Sheet217[PO]),1)) = "Y", 1, 0)
Var DBStatus = If( UPPER(left(trim(Sheet217[DB]),1)) = "Y", 1, 0)
Var Ration = DIVIDE( (BIStatus + CDStatus + ToolStatus + POStatus + DBStatus), 5)
return Ration
 
MahyarTF_0-1664845068621.png

 

Thanks for Kudos,

Please mark it as solution if it helps

Mahyartf

Thanks, 

like to understand a bit more about the UPPER(left(trim(sheet217[BI], as BI I can understand it the the table but the rest of the query.. 

SHEET217 ? Is a bit confusing 

danextian
Super User
Super User

Hi @DavidGM ,

 

For this, I would unpivot the data first before making any calculations. Once unpivoted, I can then count the number of N per user and then use the count in calculating for the percentage of completion.

 

Here are the measures I'd use:

 

Count of Incomplete = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[EY #] ), 'Table'[Value] = "N" )
)
Progress % = 
1 - DIVIDE ( [Count of Incomplete], 6 )
Training Status = 
IF ( [Count of Incomplete] = BLANK (), "Y", "N" )

 

 

Sample result:

danextian_0-1664842175657.png

 

Please see attached pbix for the details.

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

It worked well for me! Thanks

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors