IF AND Contians/IN

[rsj88]

Hi i have the below which works

IF(AND(Table1[Colour]="Red",Table1[Car]="Audi"),1,0)

I need add more criteria to the formula. eg:

IF(AND(Table1[Colour]="Red","Blue","Green",Table1[Car]="Audi","BMW"),1,0)

How would i rewrite the above?

Thanks

 

[JustynaMK]

Hi rsj88,

If you are looking for a DAX solution ("new column"), then you can try the following measure:

AndMeasure = IF(TableRank[Name] IN {"Tim","Dave","Jen"} && 'TableRank'[Group] IN {"A","Z"},1,0)

So, in your case, it'd be:

AndMeasure = IF(Table1[Colour] IN {"Red","Blue","Green"} && 'Table1'[Car] IN {"Audi","BMW"},1,0)

 

[rsj88]

Hi,

The above does not mark "A single value for column cannot be Determined" error

Thanks

 

[JustynaMK]

Hi,

You need to use "New column" instead of "New measure" in order to retain a row context -

If you use my formula in "New measure" then you'll get the "A single value cannot be determined" error.

However, if you need to build a "New measure", then you need to use some sort of aggregator to make sure that the measure always returns one single value, e.g. SUM or MAX. If that's the case, it would help to paste some screenshots to show what exactly you are trying to achieve (e.g. are you going to use this measure in a table or on a chart, how this visualizaiton looks like, is it affectedb y some filters?).