Forum Discussion
Modeling with Date dimension to filter dimension
Hi,
I have a date dim and fact table.The fact consists of multiple dates- I chose one of them randomly as active and rest are inactive.I created measures with respect to each with userelationships and it gives me correct countOfUsers per system.The problem i face is,when i filter on the System column from fact with year column on dimension,it doesnt give me correct values because of the active reltionship in place.How do i acheive this;
Example:
The issue here is: When i slice by Year from Date_D and look into Application column in a visual,it doesnt give me correct data because of the joins between date and Usage_F tables.I just gives me application names for those where the active relationship applies.How do i make this interact correctly and privide me correct numbers.
1 Reply
Hi Anonymous,
If you need to filter multiple data fields at the same time, then you will need to consider applying a role-playing dimension design pattern, as you can only use a single active relationship to a table at a time (unless you are creating specific measures to handle the relationships).
This will require multiple instances of the same table in your model, with their own relationships. It's quite a lengthy topic to answer within the post, but I suggest reviewing the following articles to see how this can be best suited to your particular challenge:
- Role-playing Dimension in SSAS Tabular Models (this will work for Power BI too; have included because the second post below references it and it might be useful for context)
- Role Playing Dimensions in Power BI (follow-up to the first post, but Power BI specific)
Hopefully this will give you the approach you need to alter your model to fit your requirements.
Good luck!
Daniel
