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 Of...
  • 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

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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks now Its working fine !!!