Using the logic for calculated column in ameasure

[jak8282]

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

[poweringthru · Accepted solution]

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"
)