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
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
Hi @stevejpage are you able to post a picture of the relationship view so that we can better understand the current issue?
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
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.
Hi @stevejpage I would suggest changing the Relationship direction from Both to Single, that should then eliminate the duplicates coming through?
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"?
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
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
I've gotten a little further, and have uploaded the Desktop file to
https://www.sendspace.com/file/bz07lm
AllSiteAccessProfileID = FILTER(DISTINCT(UNION( DISTINCT(GroupSiteAccessProfileWithcriti[Site Access Profile ID]), DISTINCT(GroupSiteAccessProfileWithreque[Site Access Profile ID]) )), (not(isblank([Site Access Profile ID]))))
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |