Measure with a reference column from a separate table

[Anonymous]

I currently have these two tables, with 1 to many relationship between ‘table1’[id] and ‘table2’[meterId].

table1

Id	meterCategory	meterSubCategory	meterName
1	VM	MSC1	MN1
2	VM	MSC1	MN1
3	VM	MSC1	MN1
4	VM	MSC1	MN2
5	VM	MSC1	MN2
6	VM	MSC2	MN3
7	VM	MSC2	MN3
8	VM	MSC2	MN3
9	VM	MSC2	MN4
10	VM	MSC2	MN4

table2

resourceName	resourceGroup	meterId	quantity
R1	RG1	1	720
R2	RG2	2	720
R3	RG3	3	720
R4	RG4	4	720
R5	RG5	5	720
R6	RG6	6	710
R7	RG7	7	710
R8	RG8	8	710
R9	RG9	9	710
R10	RG10	10	710

My main goal  is to compute for cost using a measure, by multiplying a certain factor to the ‘table2’[quantity] column, based on the value of ‘table1’[meterName].

Sample computation:

If ‘table1’[meterName] = “MN1”, cost = ‘table2’[qantity] * 0.5
If ‘table1’[meterName] = “MN2”, cost = ‘table2’[quantity] * 0.65
If ‘table1’[meterName] = “MN3”, cost = ‘table2’[quantity] * 0.75
If ‘table1’[meterName] = “MN4”, cost = ‘table2’[quantity] * 0.95

Can you help me come up with a measure to do this? One thing to note is that I am using direct query method only, since the data are stored in an Azure database and would not like to resort to changing to import mode.

Thanks!

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

Sumx( Table2, Switch(related(table1[meterName]) ,
"MN1", table2[qantity] * 0.5
"MN2", table2[quantity] * 0.65
"MN3", table2[quantity] * 0.75
"MN4", table2[quantity] * 0.95
))

[Anonymous]

You nailed it! Thanks a lot @amitchandak!

I just added a comma (,) at the end of each line, then ended it with BLANK() as the ELSE statement. 🙂