Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

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 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

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

tomfox_0-1649274892023.pngTop 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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

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.

StevenT_1-1649273274773.png

 

 

Hi @Anonymous ,

 

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

tomfox_0-1649274892023.pngTop 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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.