cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.
Learn with me on YouTube @DAXJutsu.
Helper I

It worked well for me! Thanks

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors