Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

getting data from a table not linked in the relationship

Hi, I'm using a data model that I cannot change.  There is a table outside of the relationship where I need to get it's data but without the join i can't seem to use the LookupValues function. Sin...
Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi Anonymous ,

     

    Actually, I think I found an easier way. I did an example here:

    Top left is the fact table (in your case WIP) and beneath a dimension table (in your case RallyEpics). The tables do not have a relationship. I created a measure that calculates the sum over the product of the Quantity and the respective Average value by looking up the respective Type for each ID. Here the measure:

    TomsRelationShipMeasure = 
 SUMX( TableFact, TableFact[QUANTITY] * 
        CALCULATE( VALUES( TableDim[Average] ),
                   FILTER( TableDim, 
                           TableDim[Type] = TableFact[ID] ) ) 
 ) 

     

    I am sure you can build something similar for your use case. 

     

    Here more inspiration (that is the link that I used for the above formular as well):

    How to relate tables in DAX without using relationships - SQLBI

     

    Does this help? 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks Tom. I would look forward to learning how to create the table variable. I know they can be done in SQL Server but I've never created one outside of it and definitely not in PowerBI.

Anyway, here is the image of the two tables. They are not currently linked via a relationship, which is why i have to find an alternative to find data in the RallyEpics Table that isn't in our WIP table. The line indicates where the link should be but currently isn't . The rectangle shows the data i want to get.

 

 

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Hi Anonymous ,

 

Actually, I think I found an easier way. I did an example here:

Top left is the fact table (in your case WIP) and beneath a dimension table (in your case RallyEpics). The tables do not have a relationship. I created a measure that calculates the sum over the product of the Quantity and the respective Average value by looking up the respective Type for each ID. Here the measure:

TomsRelationShipMeasure = 
 SUMX( TableFact, TableFact[QUANTITY] * 
        CALCULATE( VALUES( TableDim[Average] ),
                   FILTER( TableDim, 
                           TableDim[Type] = TableFact[ID] ) ) 
 ) 

 

I am sure you can build something similar for your use case. 

 

Here more inspiration (that is the link that I used for the above formular as well):

How to relate tables in DAX without using relationships - SQLBI

 

Does this help? 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/