Need Correction in my modelling

Arrow2727 — Wed, 06 Sep 2023 09:56:38 GMT

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

Re: Need Correction in my modelling

JoeBarry — Wed, 06 Sep 2023 11:24:51 GMT

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

Re: Need Correction in my modelling

Arrow2727 — Wed, 06 Sep 2023 14:04:54 GMT

Thank you for giving your valuable feedback and I will apply as you suggested thankyou verymuch

topic Re: Need Correction in my modelling in DAX Commands and Tips

Need Correction in my modelling

Re: Need Correction in my modelling

Re: Need Correction in my modelling