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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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