Lookup from different table in Dax Measures

[spycein]

Hello Everyone,
I am looking for a dax function measure for powerpivot report which would return the value based on a lookup value from different table.
For Example,I have 2 tables as follows
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]City Name[/TD]
[TD]State_Code[/TD]
[/TR]
[TR]
[TD]Mumbai[/TD]
[TD]MH[/TD]
[/TR]
[TR]
[TD]Pune[/TD]
[TD]MH[/TD]
[/TR]
[TR]
[TD]Chennai[/TD]
[TD]TM[/TD]
[/TR]
[TR]
[TD]Noida[/TD]
[TD]UP[/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Product_Name[/TD]
[TD]City[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Mumbai[/TD]
[TD]100000[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Noida[/TD]
[TD]70000[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Chennai[/TD]
[TD]80000[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Pune[/TD]
[TD]60000[/TD]
[/TR]
</tbody>[/TABLE]








The logic for the function i am looking for would be
if city name pertains to State_Code = MH then Tax Rate Would be 20%,
if city name pertains to State_Code = TM then Tax Rate Would be 18%
if city name pertains to State_Code = UP then Tax Rate Would be 15%
I dont want a calculated column, i want to create a dax measure for tax value.
Thank you so much in advance.
Best Regards
Shib

 

[VBA Geek]

Normalize your tax % into the dimension table and then it should be easy:

 

[citizenbh]

Create the third table:

State_Code, Tax Rate
MH, 20%
TM, 18%
UP, 15%

and define relationships.

 

[spycein]

Thank you so much for your reply. But i am looking for a dax measure fuction. i dont want create any further table or column.
Kindly share your views on this.
Best Regards
Shib