Data modeling help for a newbie

[MisterJ]

Hi,

I've only started learning how to use Power BI to replace Excel / Powerpoint reporting and I've only been doing self-study on the foundations and am a bit stumped with data modeling.

I have an existing Excel report where I mainly use INDEX-MATCH, CONDITIONAL FORMATTING, and a few basic stuff for my visuals and I'm finding it quite a challenge translating it to Power BI (especially in the absence of pivot tables).

Here's what my data looks like:

(can't post the actual data set for confidentiality reasons, but it is in the area of 10,000 rows at any given time)

and how the output should be:

So a bit more context, I'm monitoring training completion data for multiple groups of employees. The courses are further categorized but I have my column headers labeled in 2 ways, namely:

• Completion per course names (specific courses launched during a campaign period)
• Course group type (where I track ANY course title completed under that group, not needing the actual course name displayed)

The data set is made up of multiple rows, with employee names appearing several instances for having multiple courses enrolled/tracked and is flagged with a completion status column.

Here's what I've done so far: 

1. Created a new table named 'Main' and plotted the 'Employee No.' as the unique reference / index column and created a relationship to the 'Training Report' table.
2. Created a new column and used this DAX formula:
   Column Name = IF(LOOKUPVALUE('Training Report'[Completion Status],'Training Report'[Employee ID], Main[Employee No.],'Training Report'[DimTrainings.Course Name],"Course Name")="Completed","Y","N")
3. It returns either "Y" (for Completed) or "N" (for anything else)

Now, I got it working for individual course names but can't get it to work with a course group (Ex: 'Mental Health' course group has around 30+ courses)

In addition, I use slicers to filter the visuals according to the org structure, so would like to have that same functionality in Power BI. I have played around a bit following the star schema model (using fact tables and dimension tables) but still can't get the result I need when I start plotting my data in the visuals.

Another thing I'm tracking is the course completion rate per manager (this is where the pivot table function in Excel comes into play) and I can't seem to get it right in Power BI. I tried using CALCULATE, COUNTA, & FILTER combo on a separate table, but was wondering if it's possible to have them all in one table?

P.S. I was hoping to use the 'gauge' visual for the completion rate

Apologies if the post was too long, and as you can see, I'm having a bit of a challenge expressing what I want to achieve so I hope that made sense. Any guide will be appreciated greatly! Thank you in advance!

[BuckyKat37]

For doing this type of grouping I've found it much easier to build a separate Excel file that lists all training titles in one column and then add the group name for each training in a column next to it. Then you can import the Excel file into Power BI and do a merge of the Excel file into your main table. Then you can use that column in any visual without needing to write any code.

I'm not in front of Power BI at the moment so I don't want to comment about the other things and lead you down the wrong path if I've got some incorrect things.

Also, the Matrix visual is Pivot Tables for Power BI.

[MisterJ]

Hi @BuckyKat37 and thank you for your input.

I've done that (list the unique course names and categorized them accordingly) but what puzzles me is when I try to pull them into a new table for the output / data dump that only shows "Y" and "N", the same formula doesn't work for the group with 30+ courses. Maybe I'm doing the formula wrong or rather should be doing it differently where the logic is "if any of these 30+ courses were completed, return "Y", otherwise, return "N". I can't use the IF-OR combo as it would be tedious to manually list all the 30+ courses in the argument) 😞