cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nbitabc
Frequent Visitor

Retrieve value into one Dimension table into another dim table

We are new to PowerBI so please excuse if this question is too basic.

 

Have been trying to fetch a related value in a Dimension from another dim table but it is not working as expected.

 

Example - lets there are two Fact tables Sales Fact 7 Inventory Fact joined to two confirmed dimension tables with many to one relationship - Office Location, Region. 

I am trying to add a column into Office Location table that will display Region Text for all records where Office Location = 'Remote'

Something like,

Combined Location Column = IF Office Location = "Remote" THEN REGION (From Region Dimension) Else Office Location.

 

That way when user brings in the Calculated Combined Location column it will override the text for all 'remote' office location and display 'Region' 

Region text only exists only in Region Table and there is no direct link with Office Location table. 

 

Any help is appreciated.

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @nbitabc ;

I'm really sorry that I reconstructed the model, but there are still some deviations and I can't understand your logic. If you can modify it in my file and share the result you want to output, maybe it will be better? Or share your files after removing sensitive information? Looking forward to your reply!

Best Regards,
Community Support Team_ Yalan Wu

View solution in original post

7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

Hi, @nbitabc ;

I think it is more convenient to create measure, but it is difficult to create column, so another table needs to be created. If measure is used, please try:

Measure = IF(MAX('Office_Loc_Dim'[Office_Location])="Remote",MAX('Region_Dim'[Region]),MAX('Office_Loc_Dim'[Office_Location]))

The final output is shown below:

vyalanwumsft_0-1637720493746.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you. This will work in our requirement

v-yalanwu-msft
Community Support
Community Support

Hi, @nbitabc ;

I'm really sorry that I reconstructed the model, but there are still some deviations and I can't understand your logic. If you can modify it in my file and share the result you want to output, maybe it will be better? Or share your files after removing sensitive information? Looking forward to your reply!

Best Regards,
Community Support Team_ Yalan Wu

Hi @v-yalanwu-msft ,

 

Sorry if my explaination was not clear earlier.

 

Here is an example file with the scenario -

In the data model, you can see Office Location and Region are two different dimension tables and have no direct link between them. 

Related_Example_pbix 

 

nbitabc_0-1637682524087.png

 

 

Here is mockup desired output -

Desired_Output 

Need to create the Combined Office Loc-Region field in PowerBI report that can used with both Sales Fact and Inventory fact tables

 

nbitabc_1-1637682585088.png

 

Hope this clears this clears it up. Thanks for your help.

 

v-yalanwu-msft
Community Support
Community Support

Hi, @nbitabc ;

I'm really sorry that I can't fully understand the output result that your data model has been hoping for through your description. Based on my understanding, I made a simple sample:

1.simple file‘s model

vyalanwumsft_0-1636694439329.png

2. create a column .(Here you could change the 'B' to 'Remote'.)

Column = IF([Office Location]="B",RELATED('dimension'[Region]),[Office Location])

The final output is shown below:

vyalanwumsft_1-1636695106871.png

If this doesn't work for your model, can you make a simple file with the result you want to output along with the scene?

The most important parts are:
1. Sample data as text or screenshots.
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Looking forward to your reply!
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yalanwu-msft,

 

Thank you for your response. 

In my case Region and Office location are two different Dimension tables and are not directly related. So RELATED functon will not work here. 

 

nbitabc_0-1637605443511.png

 

Ideally would like a single combined column that can be used along with both Sales Fact and Inventory Fact table in a single table. 

Example output -

 

nbitabc_1-1637605913564.png

 

Hope this is clear. Appreciate your assistance !

lbendlin
Super User
Super User

Most likely the dreaded LOOKUPVALUE() is what you are , erm, looking for.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors