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
VKravc1
Frequent Visitor

Multiple Column Relationship with the same date in a single model

Hi eveyrone!

Unfortanetaly, didn't find relevant issue.

So, i have 2 data tables with the same columns (solely different between these tables is one from xlsx static file and other updates dymanicly). Both tables have columns "Location before" and "Current location". Connection between tables works fine when i create separate data model with distinct values for each one columns (lets say "Location before model" and "Current location model").

VKravc1_0-1671442439350.png

 

But i don't like that both "Location before model" and "Current location model" have the same unique data, so it would be more logically if i used the same data model for both columns instead of creating a separate one for each:

VKravc1_0-1671443017438.png VKravc1_1-1671443048287.png

 

But obivously, i can't create two active connections between data table and data model and i don't see simple ways for duplication of columns - some data in columns can be empty, some with the same values, for example:

VKravc1_0-1671444492771.png

 

 

Thanks for any help or tips! 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@VKravc1 , Add inactive join and activate in a measure using userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

 

If that does not work, then you need to create two copy of dimension

View solution in original post

So finally it works - I just need to add second filter to CALCULATE and it must be exactly second table in USERLEATIONSHIP. So the correct measure is:

TestTest = CALCULATE('Calendar'[TotNumber],
USERELATIONSHIP('Model: Location'[Current Location:], 'HlpTTS_data'[Location of residence before displacement]),
USERELATIONSHIP('Model: Location'[Current Location:],'kobo_data'[Location of residence before displacement])
)

@amitchandak , thank you so much for your tip!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@VKravc1 , Add inactive join and activate in a measure using userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

 

If that does not work, then you need to create two copy of dimension

@amitchandak , thanks for your feedback!

first one solution not exactly works - model looks like that:

VKravc1_1-1671461053021.png

So for "current location" i have active connection and for "location before" inactive. 

I created measure:

 

TestTest = CALCULATE('Calendar'[TotNumber],
        USERELATIONSHIP('Model: Location'[Current Location:], 'HlpTTS_data_'[Location of residence before displacement]) )

 

where [TotNumber] - total number of items, which we count, in this case - number of displaced. But i can't use argument from second table, because USERLEATIONSHIP takes only two arguments. So as a result, it returns correct data before 8th of Dec (because before it date it takes from "HlpTTS_data" table and incorrect since 8th of Dec (from this date all data take from the second table).

VKravc1_3-1671461880314.png

 

If USERELATIONSHIP unaccaptable for it case (or if i don't see correct solution), could You clarify, pls, about creation of "two copy of dimension"?

 

Thanks in advance!

 

 

 

So finally it works - I just need to add second filter to CALCULATE and it must be exactly second table in USERLEATIONSHIP. So the correct measure is:

TestTest = CALCULATE('Calendar'[TotNumber],
USERELATIONSHIP('Model: Location'[Current Location:], 'HlpTTS_data'[Location of residence before displacement]),
USERELATIONSHIP('Model: Location'[Current Location:],'kobo_data'[Location of residence before displacement])
)

@amitchandak , thank you so much for your tip!

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.