Data Model Design Help

[rajulshah]

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!

[v-jayw-msft · Accepted solution]

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.

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.

[v-jayw-msft · Accepted solution]

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())

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.

[v-jayw-msft]

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.

[rajulshah]

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?

[v-jayw-msft · Accepted solution]

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.

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.

[rajulshah]

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:

It may be

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

So, it will not work.

Is there any other alternative solution?

[v-jayw-msft · Accepted solution]

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())

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.

[rajulshah · Accepted solution]

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.