cancel
Showing results 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.

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

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.

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

It worked well for me! Thanks

Announcements

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

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors