Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Check the numbers present- DAX- conditional check

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's avatar
    Anonymous
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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