getting data from a table not linked in the relationship

[Anonymous]

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 

[v-jayw-msft]

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

[Anonymous]

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 · Accepted solution]

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/

[tackytechtom]

Hi @Anonymous ,

It might be possible to create a table variable in the measure and use this to do filtering etc. 

You might wanna provide example tables and what you exactly would like to achieve 🙂

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