Greater Houston Texas Power BI Users

Expand all | Collapse all

Relationship Error Message

  • 1.  Relationship Error Message

    Posted 09-14-2018 11:05 AM
      |   view attached
    Hello PUGgers,

    I need some help with a data model.  I am trying to run analysis on internal skills assessment results and I am having trouble assigning a relationship between certain tables.  I am getting this error when I try with all except for 1 table.  Which is a table I actually want to replace, as it only contains 1 single user's subset of data.

    I think I understand why it's generated for the Transformed Results table as it contains all 70 users' results (so Question Names are repeated multiple times in that column), but I don't understand for it Q Add Data since it lists each question once along with other data.  As does the table where the relationship works.

    Any recommendations?


    ------------------------------
    Holly Kramer
    Digital Design Application Specialist
    Interior Architects
    Houston TX
    713-481-3161
    ------------------------------


  • 2.  RE: Relationship Error Message

    Bronze Contributor
    Posted 09-14-2018 12:54 PM
    Hi Holly,

    The easiest way to deal with this is to create a table which has each question in it once and then connect that table to each of the tables that have questions.

    I would start with whichever table contains all the possible questions, even if they are repeated.  Right click the query with that table and pick Reference.  You can also pick Duplicate but that means that Power BI will have to do all the steps again whereas Reference will just copy the output of that query and then you would add steps.  If it's a relatively small query that doesn't take too long to run, you can get away with Duplicate, but if it's a lot of data with a lot of steps that takes more than a couple of minutes to run, Reference is your best bet.

    In your new query, select the column with the questions and then do Remove Other Columns.  Then Remove Duplicates on the remaining column.  Name the query whatever you want and load it to the data model.

    Then draw the relationships between that table and any of the other tables you need to connect it to.

    Andrew

    ------------------------------
    Andrew Machiano
    Business Analyst
    Heath Consultants Incorporated
    Houston TX
    713-844-1216
    ------------------------------



  • 3.  RE: Relationship Error Message

    Posted 09-14-2018 03:46 PM
    Thank you Andrew - I will try that next.  I did find a video that suggested making a Key by appending columns together and removing duplicates.  This worked to draw the relationships, but I'm still not able to get some of the data from different tables to adjust based on slicers or other visual selections.  Pretty sure this data model is a mess at this point.  I may try to start over by importing my different sources and creating a new table from appended columns.

    This particular report was supposed to be a 'quick' fix anyway.  I actually planned on bringing the data model to the next PUG and getting some help with transformations.  The original data output from the assessment agency is not Power BI friendly, and I can't afford to edit that export each time in Excel.

    ------------------------------
    Holly Kramer
    Digital Design Application Specialist
    Interior Architects
    Houston TX
    713-481-3161
    ------------------------------



  • 4.  RE: Relationship Error Message

    Posted 09-14-2018 05:31 PM
      |   view attached
    All fixed!  After making both a User Name Key and a Question Name Key, I decided to delete all my relationships and start over.  I drew all the relationships off of the Key tables and now it all works.  Probably a best practice anyway, but I was relying (or just being lazy) on the auto created relationships that PBI places based on naming and it was just not clean.


    ------------------------------
    Holly Kramer
    Digital Design Application Specialist
    Interior Architects
    Houston TX
    713-481-3161
    ------------------------------



  • 5.  RE: Relationship Error Message

    Bronze Contributor
    Posted 09-14-2018 06:33 PM
    Awesome!  Glad you fixed it!

    I had to learn that lesson the hard way and beat my head up against that same wall a LOT.

    Now whenever I work with people who are just getting started I always advise them to delete all the auto relationships that PBI draws on the initial data load and to start from scratch.  It sounds like a pain in the butt but it serves two purposes:  It gets rid of any improper relationships and it helps people truly understand their data model.  Also, DAX works WAAAYYYY better when the relationships are right.

    Andrew

    ------------------------------
    Andrew Machiano
    Business Analyst
    Heath Consultants Incorporated
    Houston TX
    713-844-1216
    ------------------------------