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

Hi Anonymous ,

 

You could use selectedvalue() or values function to get a value or list from one table and use it to compare with column from another table.

https://docs.microsoft.com/en-us/dax/selectedvalue-function 

https://docs.microsoft.com/en-us/dax/values-function-dax 

measure = calculate(sum('table1'[value]),filter(allselected('table1'),table1[ID] in values('table2'[ID])))

Or you could take a look at the userelationship() function.

https://docs.microsoft.com/en-us/dax/userelationship-function-dax 

 

Best Regards,

Jay