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.

Since they aren't joined in the relationship in the data model is there a way i can use DAX to connnect to the table not in the model and get the data i need?

Thanks, Steven 

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/

4 Replies

  • 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/

  • 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