Forum Discussion
Help Needed w/ Data Model :-(
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
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])
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
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
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.
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