Forum Discussion

jak8282's avatar
jak8282
Helper III
3 years ago
Solved

Using the logic for calculated column in ameasure

Hi, 

 

I have the following code

 

 

Count = 
IF (
   CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[First Name] ), "dummy" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[First Name] ), "regression" ) ||
    LEFT ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[First Name] ), 4 ) = "test" ||
    RIGHT ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[First Name] ), 4 ) = "test" ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[First Name] ), "condition" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[First Name] ), "invalid" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Full Name]), "dummy" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Full Name] ), "regression" ) ||
    LEFT ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Full Name] ), 4 ) = "test" ||
    RIGHT ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Full Name] ), 4 ) = "test" ||
    CONTAINSSTRING ( 'FINLIVE - Contact Details (Email & Phone)'[Full Name], "condition" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Full Name] ), "invalid" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Email Address] ), "dummy" ) ||
    CONTAINSSTRING ( LOWER ('FINLIVE - Contact Details (Email & Phone)'[Email Address] ), "regression" ) ||
    LEFT ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Email Address]  ), 4 ) = "test" ||
    RIGHT ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Email Address]  ), 4 ) = "test" ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Email Address] ), "condition" ) ||
    CONTAINSSTRING ( LOWER ( 'FINLIVE - Contact Details (Email & Phone)'[Email Address]  ), "invalid") ,
    "Test Records",
    "Clients"
)

 

You cant really have Column references in a measure without some aggregation.  How coulkd I use this code in a measure?

 

Thanks

Need Help
  • poweringthru's avatar
    poweringthru
    3 years ago

    You could have it as a calculated column on the table (since I'm only seeing reference to that one table 'FINLIVE - Contact Details (Email & Phone)', and then have your measures be:

    Total Test Records =
CALCULATE(
    COUNTROWS('FINLIVE - Contact Details (Email & Phone)'),
    'FINLIVE - Contact Details (Email & Phone)'[Count] = "Test Records"
)

    And:

    Total Clients =
CALCULATE(
    COUNTROWS('FINLIVE - Contact Details (Email & Phone)'),
    'FINLIVE - Contact Details (Email & Phone)'[Count] = "Clients"
)

     

1 Reply

  • poweringthru's avatar
    poweringthru
    Helper I
    3 years ago

    You could have it as a calculated column on the table (since I'm only seeing reference to that one table 'FINLIVE - Contact Details (Email & Phone)', and then have your measures be:

    Total Test Records =
CALCULATE(
    COUNTROWS('FINLIVE - Contact Details (Email & Phone)'),
    'FINLIVE - Contact Details (Email & Phone)'[Count] = "Test Records"
)

    And:

    Total Clients =
CALCULATE(
    COUNTROWS('FINLIVE - Contact Details (Email & Phone)'),
    'FINLIVE - Contact Details (Email & Phone)'[Count] = "Clients"
)