Measure to return different columns depending on conditions

[kimrhansen]

Hi all,

I am trying to do something like this

if the following conditon is met

Product is ship, the year is 2016 and the color is blue then the measure should take the value sales from table1

otherwise it should take the vale sales2 from table2.

Measure  = IF(and(and(product="Ships";year="2016");color="Blue");Table1[sales];table2[sales2])

It is incredible how much trouble this is giving me.

[v-qiuyu-msft]

Hi @kimrhansen,

Assume the Product, Year and Color comes from the Table1, please try to create a measure like below:

Measure = IF(and(and(MAX('Table1'[Product])="Ships",MAX('Table1'[Year])="2016"),MAX('Table1'[Color])="Blue"),SUM(Table1[sales]),SUM(Table3[Sales2]))

If it doesn't meet your requirement, please share sample data about those two tables for us to analyze.  

Best Regards,
Qiuyun Yu

[Greg_Deckler]

Are these tables related?

[spuder]

Hi @kimrhansen

is "ships", "color" and/or "year" part of one of those tables?

And should your result be only one number or will it be used in a visual that has more categories?