Retrieve value into one Dimension table into another dim table

[nbitabc]

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.

[v-yalanwu-msft]

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:

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.

[nbitabc]

Thank you. This will work in our requirement

[v-yalanwu-msft · Accepted solution]

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

[nbitabc]

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 

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

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

[v-yalanwu-msft]

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

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:

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.

[nbitabc]

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. 

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 -

Hope this is clear. Appreciate your assistance !

[lbendlin]

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