Forum Discussion

stevejpage's avatar
stevejpage
Frequent Visitor
9 years ago

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 ...
Need Help
GilbertQ's avatar
GilbertQ
Super User
9 years ago

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

stevejpage's avatar
stevejpage
Frequent Visitor
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
    stevejpage
    Frequent Visitor
    9 years ago

  • stevejpage's avatar
    stevejpage
    Frequent Visitor
    9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago
    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's avatar
    stevejpage
    Frequent Visitor
    9 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago


    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