How to get data from another table?

[Anonymous]

The green card in the pbix file and the slicer, are from different tables. That's why when I changed the "Purpose" slicer, the data in the card won't change.

I cannot change the relationship model because I have so many other tables, and it is quite complicated. Is there any DAX to make the data change when I choose the "Purpose" slicer?

I have tried to create this column in COST Table but it won't work. Plan_lookup = LOOKUPVALUE(plan[Count Plan],plan[YEAR],cost[YEAR], plan[Purpose], cost[PURPOSE])

This is supposed to be the output.

 

 

 

 

 

 

 

 

This is my pbix file link. https://1drv.ms/u/s!AoKh3nlpMpSOglsiwAttrllh1Ryi?e=QvKOh0 

Thank you in advance.

@Sean

[Anonymous · Accepted solution]

Hi，@Anonymous 

According to the sample you've provided，if you are willing to make some adjustments to the model, that would be great.

You can try to create two new tables, such as the following: 

PURPOSE = DISTINCT(UNION(VALUES(dms[PURPOSE]),VALUES(plan[Purpose])))

Year = DISTINCT(UNION(VALUES(dms[YEAR]),VALUES(plan[YEAR])))

Then you can create relationships between these tables：

When you back to the report view，you will see：

Here is the demo , please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/linli_qiuyunus_onmicrosoft_com/EWcBbXmQy-dFslys35haO00BEs5gEmTZNDdzmxNloSg-gg?e=04C4T4

For a better way to optimize your existing model, we recommend using the star schema. https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

It describes star schema design and its relevance to developing Power BI data models optimized for performance and usability.

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , please find the various ways to copy data from one table to another. Please see if my video can help

https://www.youtube.com/watch?v=czNHt7UXIe8