cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

Data Model Design Help

Hello,

 

I need help in designing the model in Power BI for the below case -

Below are the tables
-> Freelancer - One Freelancer can apply for more than one project
-> Project - Collection of Project
-> Project Application - Collection of Project Application. A Freelancer can apply only once on a Project. But a Project can be applied on by multiple Freelancers.
-> Dates - Calendar table; to be used as Report Level filter

 

Below is the table structure -

#Freelancers
Id
Name
CreatedOn
Skills


#Projects
Id
Name
CreatedOn
Expected Delivery Date
ClosedOn

 

#ProjectApplications
Id
Freelancer Id
Project Id
Created On


#Dates
Date
Month
Year

 

Below are the actives relationships -
> FreeLancer(Id) -> Project Application(Freelancer Id)
> Project(Id) -> Project Application(Project Id)

My requirement is, I need to display all the details of FreeLancer, Project and Project Application in a single report (Separate pages having table for each) based on Dates(Date), which needs to be a report level filter; i.e., if I have selected November 2019 in the Dates filter, below, is the expected results -
Freelancer - It will display all the details of Freelancer having created date in November 2019
New Projects - It will display all the details of Project having created date in November 2019,
Closed Projects - It will display all the details of Project having Closed date in November 2019,
Project Application - It will display all the details of Project Application, along with the name of Freelancer & Project having created date in November 2019

Any help in setting up the right ERD is highly appreciable.

Thanks in advance!

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Data Model Design Help

Hi @rajulshah ,

 

I'm sorry I probably didn't make myself clear.
When we use SELECTEDVALUE(), there's no need to create relationship. Also, we can use sync slicer without relationship.
Please refer to the pbix i have made to design your data model.
Below are some screenshots about the result.
3.PNG
1.PNG
2.PNG
 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Community Support
Community Support

Re: Data Model Design Help

Hi @rajulshah ,

 

Yes, i understand, the logic is the same. 

You just need to use SELECTCOLUMNS() instead of SELECTEDVALUE().

For example measures and results:

Measure = IF(MAX(Freelancers[CreatedOn]) in SELECTCOLUMNS('Date',"date",'Date'[Date]),1,BLANK())
Measure 2 = IF(MAX(Projects[CreatedOn]) in SELECTCOLUMNS('Date',"date",'Date'[Date])||MAX(Projects[ClosedOn]) in SELECTCOLUMNS('Date',"date",'Date'[Date]),1,BLANK())

4.PNG5.PNG6.PNG7.PNG

BTW, Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: Data Model Design Help

Thank you so much for the timely help and the solutions @v-jayw-msft ! 

The suggested approach will definitely work but it is a performance killer because we have a huge amount of data. Earlier, we did the same thing for one of our clients and it badly impacted the performance. Is there any way to optimize the performance on this approach? If not then could you please provide the alternative solutions.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Data Model Design Help

Hi @rajulshah ,

 

According to your description, please consider using Sync Slicers instead of report level filter. You can create a calculated column dates[month]&dates[year] as a sync slicer. For more details about Sync Slicers, please check below document:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicer....

To display both new projects and closed projects, please create inactive relationship between Dates[date] and Projects[CreatedOn] as well as Dates[date] and Projects[ClosedOn]. For more details about USERELATIONSHIP() function, please check below document:

https://docs.microsoft.com/en-us/dax/userelationship-function-dax.

Please let me know if you have any questions or you can share your pbix to me if you don't have any Confidential Information.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Solution Sage
Solution Sage

Re: Data Model Design Help

Thank you for the reply @v-jayw-msft !

But as per my understanding, to use sync slicer, we need an active relationship between

Dates[Date] -> Freelancer[CreatedOn]

Dates[Date] -> Project[CreatedOn]

Dates[Date] -> ProjectApplication[CreatedOn]

Otherwise, it will not apply the filter.

Is there any other alternative solution?

Highlighted
Community Support
Community Support

Re: Data Model Design Help

Hi @rajulshah ,

 

I'm sorry I probably didn't make myself clear.
When we use SELECTEDVALUE(), there's no need to create relationship. Also, we can use sync slicer without relationship.
Please refer to the pbix i have made to design your data model.
Below are some screenshots about the result.
3.PNG
1.PNG
2.PNG
 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: Data Model Design Help

Thank you for the reply @v-jayw-msft 

I appreciate the work you did. But, in my case, user can apply any type of filter on the date as shown below:
datefilter.png

It may be

-> Between
-> Before
-> After
-> Relative
-> Month
-> Year
etc.

So, it will not work.

Is there any other alternative solution?

Highlighted
Community Support
Community Support

Re: Data Model Design Help

Hi @rajulshah ,

 

Yes, i understand, the logic is the same. 

You just need to use SELECTCOLUMNS() instead of SELECTEDVALUE().

For example measures and results:

Measure = IF(MAX(Freelancers[CreatedOn]) in SELECTCOLUMNS('Date',"date",'Date'[Date]),1,BLANK())
Measure 2 = IF(MAX(Projects[CreatedOn]) in SELECTCOLUMNS('Date',"date",'Date'[Date])||MAX(Projects[ClosedOn]) in SELECTCOLUMNS('Date',"date",'Date'[Date]),1,BLANK())

4.PNG5.PNG6.PNG7.PNG

BTW, Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: Data Model Design Help

Thank you so much for the timely help and the solutions @v-jayw-msft ! 

The suggested approach will definitely work but it is a performance killer because we have a huge amount of data. Earlier, we did the same thing for one of our clients and it badly impacted the performance. Is there any way to optimize the performance on this approach? If not then could you please provide the alternative solutions.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors