Forum Discussion
Measure with a reference column from a separate table
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!
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
))
2 Replies
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
))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. 🙂