Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Multiple Relationships from same table connecting to Date Table

This is likely very intuitive and been answered before but I couldn't seem to find it.

I have data that contains a unique ID for the project and lots of milestone completion dates.

EX:
ID1 has milestone 1, milestone 2, milestone 3, etc. etc.

 

I want to graph the number of completions of each milestone per month, but am having issues.
I created a date table and created a relationship with (let's say) milestone 1. It only allows 2 relationships, so I can't correctly link all of the milestones to the date table.

I read somewhere to use the USERRELATIONSHIP measure but it threw an error because the columns aren't calculated (?)

 

Help appreciated!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Anonymous,

You can create several relationships between 2 tables however only 1 will be active the rest will be inactive and you have to use USER RELATIONSHIP to make use of them.

On yor case let's assume you have the link between date and milestone 1 active and with milestone 2 inactive you need to had the following measures

COUNT OF MILESTONE 1 = COUNT(Table[Column])
COUNT OF MILESTONE 2 = CALCULATE( COUNT(Table[Column]); USERELATIONSHIP(calendar[date] ; Table[Milestone 2 Date] ))

The use the date on calendar table on your visuals and the measures on values

Regards,
MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @Anonymous,

You can create several relationships between 2 tables however only 1 will be active the rest will be inactive and you have to use USER RELATIONSHIP to make use of them.

On yor case let's assume you have the link between date and milestone 1 active and with milestone 2 inactive you need to had the following measures

COUNT OF MILESTONE 1 = COUNT(Table[Column])
COUNT OF MILESTONE 2 = CALCULATE( COUNT(Table[Column]); USERELATIONSHIP(calendar[date] ; Table[Milestone 2 Date] ))

The use the date on calendar table on your visuals and the measures on values

Regards,
MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thank you! Some things to add in case anyone else reads this post:

You have to change the semi colons to commas and USERELATIONSHIP has one r.

 

 

COUNT OF MILESTONE 1 = COUNT(Table[Column])
COUNT OF MILESTONE 2 = CALCULATE( COUNT(Table[Column]), USERELATIONSHIP(calendar[date] , Table[Milestone 2 Date] ))

Hi @Anonymous ,

 

Thank you for the correction on the formula my error when posting 🙂

 

Regading the Comma part for your information that as to do with regional settings and some are with dot comma others with comma.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.