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
yes
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
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?