Forum Discussion
Need Correction in my modelling
Sample modeling file.pbix
elaine_csv csvdesk SMS
Hello everyone,
I'm new to Power BI, and I've received a dataset that I need to use to generate outputs for my clients.
However, I'm encountering an issue with filtering data by dates during the modeling process.
Could someone please assist me in correcting my data modeling?
Your help would be greatly appreciated, and it would motivate me to continue working with Power BI.
I have attached my sample data file for reference
Hi Arrow2727
The dataset isn't structured properly to be able to filter by date. You should design it as Star Schema
- The Date table needs to be set up correctly. This table will be then be the go to for dates in your visuals.
- There seems to be a lot of irrelevant columns in the tables, delete what you don't need. The report performance will improve
- In Options turn off Auto date/time for new files. You won't need this as you will have the date table. this will also help with performance
- In Power query, if they are relevant, convert all date columns that you need to Date
- I would create a duplicate of Opportunity and call it DIM Opportunity and just keep the column like Id and if you need them for filtering, keep Risk, Type ETC..
When the above is done, then you need to make relationships between the tables correctly. Delete all the relationships you have and start from scratch.
Examples would be
- Dim Opportunity has a one to many relationship on the Id column with all tables with the Opportunity ID in it. It will happen that the relationship with opportunity will be one to one, change this to Many to many and change the cross filter Dim Opportunity filters Opportunity
- Date Table date column one to many relationship with Opportunity, but which date column? You can only have one active relationship with a table at a time. I personally use Close date.
- Do the same above for the other date columns in Opportunity. They will be inactive, but you can use the USERELATIONSHIP Dax later to activate them in measures.
- Same with the Account, Partner Create relationships with all tables that have relevant ids in them
I hope this helps
Joe
If this post helps, then please Accept it as the solution
2 Replies
- JoeBarry
Solution Sage
Hi Arrow2727
The dataset isn't structured properly to be able to filter by date. You should design it as Star Schema
- The Date table needs to be set up correctly. This table will be then be the go to for dates in your visuals.
- There seems to be a lot of irrelevant columns in the tables, delete what you don't need. The report performance will improve
- In Options turn off Auto date/time for new files. You won't need this as you will have the date table. this will also help with performance
- In Power query, if they are relevant, convert all date columns that you need to Date
- I would create a duplicate of Opportunity and call it DIM Opportunity and just keep the column like Id and if you need them for filtering, keep Risk, Type ETC..
When the above is done, then you need to make relationships between the tables correctly. Delete all the relationships you have and start from scratch.
Examples would be
- Dim Opportunity has a one to many relationship on the Id column with all tables with the Opportunity ID in it. It will happen that the relationship with opportunity will be one to one, change this to Many to many and change the cross filter Dim Opportunity filters Opportunity
- Date Table date column one to many relationship with Opportunity, but which date column? You can only have one active relationship with a table at a time. I personally use Close date.
- Do the same above for the other date columns in Opportunity. They will be inactive, but you can use the USERELATIONSHIP Dax later to activate them in measures.
- Same with the Account, Partner Create relationships with all tables that have relevant ids in them
I hope this helps
Joe
If this post helps, then please Accept it as the solution
Thank you for giving your valuable feedback and I will apply as you suggested thankyou verymuch
