showing field crossing another table

[jppuam]

Hello,

i have 2 tables and i'm trying to cross table A with table B, (with many-to-one) in line to mantain the data that i've on table A, and map with the data that i've on table B.

I want to agregate the data like this :

- dep A, should be Master Dep A

- dep B, should be Master Dep B

- and all the others departments should be aggregated as Others,

but i'm not reach making this, can anyone see whats the problem ?

thanks,

JP

[Ashish_Mathur]

Hi,

Using the Query Editor, you can join Table A into Table B based on the 2 common columns to bring over Cost into Table B.  In Table B, you can write this DAX formula

=if('table B'[Department ID]="Dep A","Master Dep A",if('table B'[Department ID]="Dep B","Master Dep B","Others"))

Hope this helps.

[ryan_mayu · Accepted solution]

@jppuam 

is this what you want?

cost = LOOKUPVALUE('table A'[Cost],'table A'[Department ID],'table B'[Department ID],'table A'[Level ID],'table B'[Level ID])

Master Department = SWITCH('table B'[Department ID],"dep A","Master dep A","dep B","Master dep B","Others")

please see the attachment below