Forum Discussion
Help Needed w/ Data Model :-(
- 5 years ago
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
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.
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 ๐