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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TCFPA
Frequent Visitor

Is there a way to connect both of my calendar both of these tables?

I have the following data model which I would like to connect this date table to both the Skedulo Jobs and Leadsquaredcontacts.

My issue is that the Skedulo and LeadSqd tables are also connected.

 

Is the only way for me to have an active connection for each to have two separate date tables?

 

TCFPA_0-1670654102004.png

 

1 ACCEPTED SOLUTION
TCFPA
Frequent Visitor

I ended up making two calendar tables

 

View solution in original post

5 REPLIES 5
TCFPA
Frequent Visitor

I ended up making two calendar tables

 

TomMartens
Super User
Super User

Hey @TCFPA ,

 

you can have two active relationships, see the following picture:

image.png

But this comes with a price. There is a concept called "The Extended Table" mentioned here MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering (mdxdax.blogspot.com). Some time ago, I tried to provide a more accessible version here: Extended tables - the sword by my side - Mincing Data - Gain Insight from Data (minceddata.info)

If you change the existing 1:1 relationship into a *:1 relationship the extended table 
Table - ProductColor - Product will become

Table - ProductColor

This will impact the performance of your existing/future measures.

 

Of course, if used in slicers, you will gain specific capabilities for data visualization and report design as an immediate dependency between the date and product tables. This type of modeling can unlock additional data visualization treats.

 

As always, it depends ... the larger your dataset becomes, the more you will "see" the performance degration, if your dataset stays small enough, you will only see the merits of the the cross filter direction "both".

 

Hopefully, this provides some useful information and helps to tackle your challenge.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I think there is one issue with this solution, and that is called "Ambiguity"
You have 2 active paths to reach from your "Date Table" to the table name called "Table" in your model
1- Direct relationship
2- Date > Product >product color> Table
This would normally work, and you will not see any error, but you may get some inaccurate numbers in visuals where DAX is confused to follow which path from above two.
For Example  -if you have a Matrix visual, which Has one column from Date table, another column is from product color table, and you want to calculate something from Table  "Table" , In this case, you may introduce ambiguity, and a DAX expression may give you wrong results, because it can not decide, which path to follow to reach to "Table".

I tried to change the relationship as you suggested form 1:1 to *:1 but powerBI is forcing it to be 1:* instead and wont allow me to make the calendar table an active relationship.

FreemanZ
Super User
Super User

You can keep the model as it is now and activate the relationship temporally with USERELATIONSHIP function, 

https://dax.guide/userelationship/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors