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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors