Forum Discussion

Gm0's avatar
Gm0
Frequent Visitor
6 years ago
Solved

Evaluate IF based on two values in same column

Hello Community, 

I have a issue I can't wrap my head around... which I think is simple... but maybe not. 

  • I have a column with EP numbers 
  • A second with countries in which that EP is registred. 

I'm trying to get a IF statement to work to tell me if when an EP number is both registered in "X" and "Y"  (e.g. Greece & Cyprus)

the result should be a true / false. It is only true if the EP number is present in both country simultaneously. 

 

IF "For and EP number"  Has X and has Y as matching registration country (evaluated on all the values within that column for the same EP number)
= "True"

 

Thus for the table here below, the first two rows should return "True" as well as the two following one's. 

 

EPCountry
EP1469879Greece
EP1469879Cyprus
EP1523638Greece
EP1523638Cyprus
EP1565201Greece
EP1585548Greece
EP1585548Cyprus
EP1589941Greece
EP1617382Greece
EP1644558Greece
EP1654861Greece
EP1706471Greece
EP1735358Greece
EP1750538Greece
EP1750538Cyprus
EP1755483Greece
EP1830239Greece
EP1851372Greece
EP1855060Greece
EP1866492Greece
EP1871314Greece

 

Thank you very much for your help!

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Gm0 ,

    I created a calculated column to implement it. You could have a try.

    Column = 
var d = CALCULATE(DISTINCTCOUNT('Table'[Country]),ALLEXCEPT('Table','Table'[EP]))
return 
IF(d>1, "True", BLANK())

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello,

     

    Have you tried to make a measure where the functions IF and AND will be combined to one. 

    You will have to use a formula like this: measure = IF(AND(country = "Greece"; country = "Cyprus") true; false)

     

     

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Gm0 ,

    I created a calculated column to implement it. You could have a try.

    Column = 
var d = CALCULATE(DISTINCTCOUNT('Table'[Country]),ALLEXCEPT('Table','Table'[EP]))
return 
IF(d>1, "True", BLANK())

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

    • Gm0's avatar
      Gm0
      Frequent Visitor
      6 years ago

      Thanks all for your input!