Help Needed w/ Data Model :-(

[ki]

Hello community,

Hope you can help me out as I invested a lot of energy in this and couldn't get this working 😞 Still fairly new to Power BI modeling. The scenairos is the following - I hava a dataset froming from e-learning platform and want to be able to manually assign employees (or roles) to specific set of learnings and track their progress with it. The following tables are available:

- Employees Master Data - holds data like name, e-mail (this is unique identifier), country, job title, etc

- Fuse Status Data - holds data on the status of each learning plan (LP) that an employee is registered on - not started, in progress, completed; along with unique ID of the LP and some other data; have also created a few KPIs here

- Mapping - this is where I am manually creating the so-called success plans (SP) - pretty much saying that SP has an ID and then holds certain LPs

- The connections are as follows: many-to-many on SP ID and then one-to many on LP ID

Once I try to create a view for a specific employee on getting status, I get rows for each LP part of the SP duplicating every possible status instead of having one row per LP holding the real status.

Adding a few screenshots.

Hope you can help me out - I am sure this is very simple and I am missing something!

Kiril

[PaulDBrown]

@ki 

I would wager the culprit is the many-to-many relationship. To avoid this, create a dimension table (select New Table in the ribbon) for the SP ID using the following:

Dimension SP ID = DISTINCT(
                    UNION(VALUES(mapping[SP ID]), VALUES(Employees Master Data[SP ID))
                  )

Now delete the many-to-many relationship and create a one-to-many relationship between the Dimension SP ID table field and the corresponding fields in both tables.
You should now use the 'Dimension SP ID' [SP ID] field from the dimension table in your visuals, filters, filter expressions in measures etc..

See if that solves the issue.

[ki]

Thanks for the quick reply!

Created the table, but when I tried then to create relationship b/n SP ID in the new table to SP ID in the existing tables I get circular dependency error 😞

[PaulDBrown]

@ki 

Is this how you are setting it up?

[ki]

yes

[PaulDBrown]

@ki 

In that case you need to create the dimension table in Power Query by appending both tables to a new query, select the SP ID column, delete all other columns, remove duplicate rows, change the query name and load. Now create the relationships

OR

if you are sure that one of the tables has ALL the SP ID values, you can create the dimension table with DAX using:

Dimension SP ID = DISTINCT(table[SP ID])

[ki]

What about totally different approach - e.g. merging tables, so ultimately I get a table holding the SP, the LP in it, the employee and the LP status? Could that be a solution?

[ki]

Tried different options, but it seems I am doing something very wrong here. This is why I created new sample data to start fresh. Still cannot get what I want. Pretty much I want to see what is the learning plan status per person based on their role. The files: https://1drv.ms/u/s!AjmmJs5xYIuZm_xAOwQ2rwzgI5e_NQ?e=EKWmff

[PaulDBrown · Accepted solution]

@ki 

Ok, so here is the model for the sample data you provided. 

I take it that what you are trying to achieve is a table showing employees who are in the success plan, correct?

If so, we need a measure which will filter the learning plan table to show only the learning plan IDs included in the success plan table. We can then filter the learning plan status to show only these LP IDs and see the corresponding employees

The measure we need is:

SP to LP IDs =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'learning-plans'[LP ID] ),
        RELATEDTABLE ( 'success-plans' )
    )
)

We can now set up the visual using:

Learning plan [LP Name] (I'm using 'Learning plan[LP ID] because it´s easier to see what's going on)
Role [Employee Role]
Employee [Employee Name]
Learning status [LP status]

Select the visual, and add the [SP to LP IDs] measure in the "filters for this visual" in the filter pane and set the value to 1.

And you get this:

 If you only want to show the "completed" status, just use the field in the filter pane (or use a slicer):

I've attached the sample PBIX file for your reference

[ki]

Thanks! Very much in the right direction! I also got the feeling we need either addiitonal tables or measures. The only "bug" I see is when I look at the Employee Name Success Plans - Ina Izova is assigned to the BDM plan, which holds 3 Learning Plans - 102, 110, 111. And 110 and 111 show as competed. There is no data on status for 102 (I did that on purpose) and in the table we don't see it, in reality it should show smth like 102 - No Data or 102 - Not started, but I think I can solve that by playing with measures.

[PaulDBrown]

@ki 

OK. I misunderstood what you were trying to achieve. To see all the relevant LP IDs per employee we need to change the approach. (Basically we need to build the visual based on the fields from the success plan table (Role & LP ID).

So try this:

1) A measure to filter the employees which are included on the plan based on role and LP ID defined in the success plan table:

Filter employee =
VAR Filt =
    CALCULATETABLE (
        VALUES ( employees[Employee Role] ),
        RELATEDTABLE ( 'learning-status' )
    )
VAR SP =
    VALUES ( 'success-plans'[Employee Role] )
RETURN
    COUNTROWS ( INTERSECT ( Filt, SP ) )

 (we will use this to filter the visual by adding it to the filter pane and setting the value to 1)

2) A measure to bring back the status from the learning status table

Status =
VAR NumEmployees =
    CALCULATE (
        DISTINCTCOUNT ( employees[Employee Name] ),
        TREATAS ( VALUES ( 'success-plans'[Employee Role] ), employees[Employee Role] )
    )
RETURN
    CALCULATE (
        MAX ( 'learning-status'[LP Status] ),
        FILTER ( 'learning-status', NumEmployees = 1 ),
        TREATAS ( VALUES ( 'success-plans'[LP ID] ), 'learning-status'[LP ID] )
    )

 We will add this measure to the visual

Now create the visual using:

success plan [employee role]

success plan [LP ID]
employee [employee name]

[Status] measure.

Add the [Filter employee] measure to the filters on the visual and set the value to 1

In the fields bucket, select the option of "Show items with no data" for both the 'success plan' [role] and 'employee' [employee name]

And you will get this (I`ve added a couple of new rows to the learning plan table to check the results):

I've attached the sample PBIX file