Check the numbers present- DAX- conditional check

[Anonymous]

hi 

I am newbee in power bi 

I have Problem with following condition below.

i have table, consider there are two columns ID and amount , ID corresponds to the City codes of sales  and amount is Offer amount of item like this

now i have some list of ids that belong to flood affected area and the offer amount should be valid only to specific city id say 

13,61,66,74,162,164

in the above Picture red formatted cell says the offer amount has been given to city id not present in above list of city ids 

13,61,66,74,162,164

and Yellow formatted Cell says that offer amount has not given to the city id - 162 which listed in the above city id list. 

How to do that ? any Sepecfic Dax formula we can use for this ? 

TIA

[Anonymous · Accepted solution]

Hope I understood it clearly 
If city is not in flood affected list and amount is greater than zero --> red

If city is in flood affected and amount is zero --> Yellow

Please create a measure as per below code

Measure = IF(AND(NOT(MAX(Sales[ID])) IN VALUES(FloodAffected[Id_City]),SUM(Sales[Amount])>0),"RED",IF(AND(MAX(Sales[ID]) IN VALUES(FloodAffected[Id_City]),SUM(Sales[Amount])=0),"Yellow", "Niether Red nor yellow"))

In case you want to use this as a slicer then create a calculated column as per below code

Column = IF(AND(NOT(Sales[ID]) IN VALUES(FloodAffected[Id_City]),Sales[Amount]>0),"RED",IF(AND(Sales[ID] IN VALUES(FloodAffected[Id_City]),Sales[Amount]=0),"Yellow", "Niether Red nor yellow"))

[Anonymous]

i want to show this two types of anomaliesas seperated detailed table not through highlighting or foamatting option

[Anonymous · Accepted solution]

Hope I understood it clearly 
If city is not in flood affected list and amount is greater than zero --> red

If city is in flood affected and amount is zero --> Yellow

Please create a measure as per below code

Measure = IF(AND(NOT(MAX(Sales[ID])) IN VALUES(FloodAffected[Id_City]),SUM(Sales[Amount])>0),"RED",IF(AND(MAX(Sales[ID]) IN VALUES(FloodAffected[Id_City]),SUM(Sales[Amount])=0),"Yellow", "Niether Red nor yellow"))

In case you want to use this as a slicer then create a calculated column as per below code

Column = IF(AND(NOT(Sales[ID]) IN VALUES(FloodAffected[Id_City]),Sales[Amount]>0),"RED",IF(AND(Sales[ID] IN VALUES(FloodAffected[Id_City]),Sales[Amount]=0),"Yellow", "Niether Red nor yellow"))

[Anonymous]

the problem is very  simple as this the primary list 

13,61,66,74,162,164

these are flood affetced areas this may add up in future also 

any city id which has this follwing city id should have amount or offer applied(non- zero)(example-yellow row)

162 is in list its should have some amount instead of zero in that image so thats anomoly 

any city id that are not coming inside this list should be zero ( example-red row)

225 is no in the list , it should be zero instead of 5 in image  @Anonymous 

[Anonymous · Accepted solution]

@Anonymous 

This is what I'm getting with your dataset. Please refer image attached. You can replace red and yellow text in formula with anomoly or any other text that you want. This code will work fine even if in future there is addition of flood affected areas

[Anonymous]

Thanks now Its working fine !!!