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

Helper I

## How to show status of the training completion by User Name

 key User Manager BI CD Tool PO DB Trarining Complete 1 Alex Shane Y Y Y Y Y Y 2 Angela Shane Y Y Y Y Y Y 3 Andrew Shane Y Y Y Y N N 4 Jamie Chris Y Y Y N Y N 5 Joseph Chris N N N N N N 6 Smith Katherine Y - PASS Y Y - PASS Y Y Y 7 Bravo Katherine Y Y Y Y Y Y 8 Dean Lee Y Y Y Y Y Y 9 John Lee Y Y Y Y Y Y 10 William Lee Y Y Y Y N N

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
Helper I

It worked well for me! Thanks

5 REPLIES 5
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

Thanks for Kudos,

Please mark it as solution if it helps

Mahyartf
Helper I

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..

Helper I

SHEET217 ? Is a bit confusing

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:

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.
Helper I

It worked well for me! Thanks

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors