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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Benedictmooray
Frequent Visitor

Relationship of more than one column

Hi, 

 

As an example i have a table University A and table University B and in University B i have 3 columns like Home, Other and Outside. I had to link these 3 columns (Home, Other and Outside) to coumn postcode in Table University A. Though i cannot create 3 relationships to another table, i tried to use USERELATIONSHIP calculation to create kind of dummy relationships. 

I now want to put it into visualisation and want just the names "Home, Other and Outside" to be used as slicers meaning if i click e.g on Home it should bring up all the postcodes from home column from Table University B and the postcodes from University A.

 

Hope i could be able to explain it better and please help.

 

Thanks

7 REPLIES 7
samioberoi
Helper I
Helper I

Hi, 

 

Thanks for replying. But i want to make it dynamic, so that if i click on e.g Home and it should bring the postcodes only related to that and similarly for others. The problem is that i am unable to add the measures created with Userelationship function to the filter. 

Thanks

Migasuke
Super User
Super User

Hi,

I think what you need is to create a new column which combine Home, Other, and Outside. Then you can use this column instead of 3 columns.

If you send sample of Table A and B I am sure we can  help you!



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Hi Migasuke,

 

Thanks for your reply. Sorry, first of all it is difficult to share the actual data. Secondly, i don't want to combine the Home, Outside and Other columns because i want to use these as slicers on the visualisation canvas and want to keep those separate anyway. 

I hope there is going to be a way out to use these column names (Home, Outside and Other) as slicers after linking these to the Postcode column in Table University A through USERELATIONSHIP calculation. 

Looking forward to hear from you soon.

Thanks

Okay so just let me clarify:

1. One table has Postcode
2. Second table has Home, Outside and Other
3. Postcode should be connected with all 3 columns (meaning 3 relationships would be needed)
4. You want to use Home, Outside and Other for slicers

Anything I missed or said wrong?

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Hi, 

 

That is absolutely right. Just to give you some more information about what i did to create the relationship through DAX is I created the following DAX USEREALTIONSHIP calculation e.g for relating column Home from University B table to column Postcodes from University A table.

 

calculate(
count(Table-University B [column(Home)],

values(Table-Universty A[column(Postcodes)]),

USERELATIONSHIP(

       Table-Universty A[column(Postcodes)],

Table-University B [column(Home)]

))

 

I may be wrong in doing this, but if i am correct then i want to know how i can use these relationships using USERELATIONSHIP measures as slicers on dashboard, so that if i click on  column Home slicer on dashboard all the postcodes from POSTCODES column linking to column HOME should just show up and same for others. 

For any other questions on this please let meknow and thank you so much for all your effort to help.

Regards

Hi,

I think you can do a simple trick. Since you only keep values in Home, Others etc. which match. Simply use visual level filter and unfilter Blank. Values which are blank are not connected to the column you use:

Migasuke_0-1682978178369.png

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Hi,   

Thanks for replying. But i want to make it dynamic, so that if i click on e.g Home and it should bring the postcodes only related to that and similarly for others. The problem is that i am unable to add the measures created with Userelationship function to the filter. 

Thanks

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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