How to write if statement with multiple conditions to create a calculated column

Question

so this might be very easy but somehow i dont find a working solution. My problem is as follows:

in a tabular model in ssas i want to create a calculated column
every row of another column should be checked if a certain condition is met and depending on which condition is met, a value should be returned into the calculated column

example:

i have a text column A which contains the following strings (each in a different row):

"DtD 24"; "SGK 12"; "STD 99" etc.

now what i want to do, is write a formula which checks the above mentioned column row-by-row, and

when it finds "12" somewhere in the string then it should return "12" as value for that row
when it finds "24" somewhere in the string then it should return "24" as value for that row

... and so on for like 4 different cases.

I tried to find a solution with switch(), with if(), with find() but none seem to work. has anybody some tips for me what would be a smart way to solve this problem?

thank you very much in advance!

Welcome to SO. Please provide some samples of what you have attempted so far and I'm sure you'll get some help :) — DaggeJ
thank you for the tip :) i'll make sure to include my trys next time. luckily @janvanwerkhofen already solved my problem in his answer below. — nox

janvanwerkhoven janvanwerkhoven · Accepted Answer · 2019-09-11T12:11:34

This formula should work for you. You can expand it to check for more numbers by adding IF statements. Do check the SSAS version you are running as the CONTAINSSTRING function only works from >= SSAS 2019 CTP2.3.

Column =
IF (
    CONTAINSSTRING ( 'Table'[Column A], "12" ),
    12,
    IF (
        CONTAINSSTRING ( 'Table'[Column A], "24" ),
        24,
        IF ( CONTAINSSTRING ( 'Table'[Column A], "99" ), 99, BLANK () )
    )
)

If you are running on an older version you can try the less clear SEARCH function:

Column =
IF (
    (SEARCH("12", [Column A], 1, 0) > 0),
    12,
    IF (
        (SEARCH("24", [Column A], 1, 0) > 0),
        24,
        IF ( (SEARCH("99", [Column A], 1, 0) > 0), 99, BLANK () )
    )
)

How to write if statement with multiple conditions to create a calculated column

1 Answers