Relationship Problems

[stevejpage]

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

[GilbertQ]

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

[stevejpage]

Here are the tables with the Security Group link made

[GilbertQ]

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

[stevejpage]

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. 

[stevejpage]

[GilbertQ]

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

[stevejpage]

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. 

[GilbertQ]

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

[stevejpage]

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

[v-yuezhe-msft]

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

[stevejpage]

[stevejpage]

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]))) 

[v-yuezhe-msft]

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

[stevejpage]

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

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

[GilbertQ]

Hi @stevejpage

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.

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.

And finally here is a link to your original PBIX file with the above changes added.

TEST1.pbix

[stevejpage]

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 Group	Site Access Profile ID	Site Access Profile Alias	List of Valid Sites	List of Requested Sites	List of Critical Sites
PASADENA	33	PPD Dispatch Roam	Bev Hills P25	Pasadena	Pasadena
			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

[v-yuezhe-msft]

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.

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