Forum Discussion

Crowbarella's avatar
Crowbarella
New Member
9 years ago

Trying to pull data from unrelated table into a matrix (Workaround Found)

I have two tables, one with actual data and one that's basically just a lookup based on 3 fields (Class, group, and date). I'm trying to pull a single field from the lookup table into a matrix visual with all the other fields pulled from the first table. The problem is that every method I've found ends up summing the entire lookup column, rather than actually filtering the data according to the class and date range selected. I can't relate the two tables directly because the app keeps saying neither have unique values, even though, when concatinating the 3 lookup fields, it is (or at least should be) unique on the lookup table.
 
Thoughts?
Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Power BI does not support creating a relationship that uses multiple columns.  The typical work around is to create a calculated column that concatenates the columns into 1.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        So, if you successfully created a relationship (and make sure the relationship is in the correct direction -- lookup table is the "one side" and data table is the "many side") -- the best debugging technique is to create a calculated column in the datatable like:

          Dbg = RELATED(LookupTable[SomeLookupColumn])

         

        Which you expect to pull down values into the fact table, but... you will probably find they don't?

         

        I'd check for extra/unexpected whitespace around values?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Crowbarella,

     

    In this scenario, we could create a bridge table which has unique value. Then the relationship could be: many -> unique<- many. If one table has all the data, the formula could be ([class] could be [group] or [date]):

    BridgeTable = values('actualtable'[class])

     Or union all the values:

     

    BridgeTable =
DISTINCT ( UNION ( VALUES ( 'actualtable'[class] ), VALUES ( 'lookup'[class] ) ) )

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Crowbarella,

     

    Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer as solution if it's convenient for you? That will be a help to the others.

    Best Regards!
    Dale

    • Crowbarella's avatar
      Crowbarella
      New Member
      9 years ago
      Well, the question as posed was never answered in a way that worked for me, but I found where the issue lied elsewhere. So, I'd calling it work around found. I was able to generate a table, filtering out the nulls, that gave me uniqueness and related that to ther original table.