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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
stevejpage
Frequent Visitor

Relationship Problems

Good afternoon all:

 

I have a series of tables between which I am trying to manage relationships. These five tables have several columns that have entries between them that are the same and on an individual basis, I can relate between two or three of the columns without any issues.

 

What I have is information from a radio system that has multiple agencies, and each of those agencies have 'Site Access Profiles' whereby users can roam between sites (like restricting on which cell tower your phone will be able to work) and between the 'Security Group' and the 'Site Access Profile' there is no problem as each profile belongs to only one security group.

 

What I have problems with is additional tables for 'Site Access Profile With Valid Sites' and 'Site Access Profile With Requested Sites' and 'Site Access Profile With Critical Sites'. I am trying to make a Matrix that shows Security Groups with Site Access Profile Name and then show the Valid Sites, Requested Sites and Critical Sites, but whenever I try to form a relationship between between more than three tables, the invalidity rears its ugly head.

 

The goal is to get the Matrix to look something like this:

 

Security Group     Profile ID    Profile Alias    Valid Sites   Critical Sites   Requested Sites

 

I'm taking suggestions, and have a smaller subset of the data in case anyone can help.

 

Thanks

 

 

17 REPLIES 17
GilbertQ
Super User
Super User

Hi @stevejpage are you able to post a picture of the relationship view so that we can better understand the current issue?

 





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

Proud to be a Super User!







Power BI Blog

Here are the tables with the Security Group link madeHere are the tables with the Security Group link made

Hi @stevejpage, thanks for the image.


What you would need to do, is to centralize all of the "Site Profile Access ID" into one table.

 

Fortunately I did make a note of a very interesting blog post from Jeffery Wang, where he goes into some detail on how to achieve this.

 

You can find the blog post here: Connect any number of tables together via a common column

 

 

 




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

Proud to be a Super User!







Power BI Blog

That's a great and very easy-to-use tutorial right there!

 

I tried that, and got most of the way there, and the DAX of 

 

All Groups = FILTER( DISTINCT( UNION( DISTINCT(GroupSiteAccessProfile [Record Identifier]), distinct(GroupSiteAccessProfileWithcriti[Record Identifier]), DISTINCT(GroupSiteAccessProfileWithreque[List of Included Requested Sites: Record Identifier]), DISTINCT(GroupSiteAccessProfileWithvalid[Record Identifier]))),not(isblank([Record Identifier])))

 

resulted in another table that had the unitque record identifiers (which are the profile assignment numbers in the system, and with the relationships in the below photo, I sitll haven't been able to get the last step of the way, which is to be able to create a matrix that will show:

1. Site Access Profile ID

2. Security Group

3. Site Access Profile Name

4. Valid Sites

5. Critical Sites

6. Requested Sites

 

Once I add the final step (Requested), it barfs out on me.

 

Also, as you can see in the photo, it lists the Critical Sites over and over and over. I can't figure that one out so that it only shows once. 

Picture 3.jpgPicture 2.jpg

Hi @stevejpage I would suggest changing the Relationship direction from Both to Single, that should then eliminate the duplicates coming through?





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

Proud to be a Super User!







Power BI Blog

No joy on eliminating the duplicates. I know there's a way to get the data I'm looking for, but maybe Power BI just doesn't do it. 

Just to confirm that you changed the Cross Filter Direction to "Single"?





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

Proud to be a Super User!







Power BI Blog

Yes, I tried chaning a number of the cross-filtering one at a time, and wound up with less data than before. 

Hi @stevejpage,

If you create a table that contains unique "Site Profile Access ID", then create relationship between the newly created table and other tables, what is the result after you create Matrix visual? Do you get expected result? If not, could you please share sample data of your tables and post desired result here?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Picture 5.jpgPicture 4.jpg

I got a syntax error when trying to create the new table using the following code:

 

All Site Access Profile ID = FILTER( DISTINCT( UNION( DISTINCT(GroupSiteAccessProfile[Site Access Profile ID]), DISTINCT(GroupSiteAccessProfileWithcriti[Site Access Profile ID]), DISTINCT(GroupSiteAccessProfileWithreque[Site Access Profile ID]), DISTINCT(GroupSiteAccessProfileWithvalid[Site Access Profile ID])))),NOT(ISBLANK([Site Access Profile ID]))) 

Hi @stevejpage

What is the error message do you get? Would you please share sample data of your tables so that I can test the sceanrio in Desktop?


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I've gotten a little further, and have uploaded the Desktop file to 

 

https://www.sendspace.com/file/bz07lm

 

 
I had a look and the issue was that the Calculated Table was not including all the related tables.
 
So if you can see below I modified it to include another table
 
 
AllSiteAccessProfileID = FILTER(DISTINCT(UNION(
DISTINCT(GroupSiteAccessProfileWithcriti[Site Access Profile ID]),
DISTINCT(GroupSiteAccessProfileWithreque[Site Access Profile ID])
 
)), (not(isblank([Site Access Profile ID]))))
And what you will need to do, is to add any additional table.
 
Then you will need to create the relationships as I did between the two tables above.
 
Power BI - Many - Many Relationships.png
 
Which once done you can then have all the data related as shown below. NOTE: In order for the relationships to work you have to put in a measure.
 
Power BI - Calculated Tables Output.png
 
And finally here is a link to your original PBIX file with the above changes added.
 




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

Proud to be a Super User!







Power BI Blog

This is probably as good as it's going to get, and the part that I was missing was the requirement to have a measure included. It's not the way I had envisioned it, preferring to have something like this be the result (when filtered down). I'd like to be able to create an additional Matrix chart which will allow us to visualize the Security Groups by color, and within that, see the Site Access Profiles.

 

Security GroupSite Access Profile IDSite Access Profile AliasList of Valid SitesList of Requested SitesList of Critical Sites
PASADENA33PPD Dispatch RoamBev Hills P25PasadenaPasadena
   Glendale P25  
   Hauser Peak  
   Montebello  
   Oat Nike P25  
   Pasadena  
   Pomona PD  

 

Maybe I'm asking for something that isn't available yet, but in a future release (or when I learn more about DAX), we'll get what we are looking for.

 

In the meanwhile I appreciate the help, and will continue learning.

 

Thanks


Hi @stevejpage,

By using the sample data in your PBIX file, we are unable to get the above Matrix visual. We should drag a field into Values of Matrix, otherwise, we will get the error message “can't display the data because power bi can't determine the relationship between two or more fields”.

I use the fields in the following screenshot to create the Matrix visual, and filter the visual by setting Site Access Profile ID to 33.
1.PNG

Please note that in the above example, you don’t need to add any additional tables, just create relationships using Site Access Profile ID fields between GroupSiteAccessProfile table and other tables. For more details, please review this attached PBIX file.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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