left(Tabelle1[ColumnA]=

[BachFel]

IF the first 18 letters (count from left) in column [Apfel] = "Objektbesichtigung" then show result from column[Birne] otherwise Blank()

Anybody who can help with the synthax?

[shebr · Accepted solution]

Hi @BachFel

You can use the following "IF(LEFT(E0[Apfel], 18) = "Objektbesichtigung", E0[Birne], BLANK())"

Or

This can be done in the Power BI Editor.

1. Go to your editor, duplicate the [Aptel] Column, right click on the column header and click 'Duplicate column'

2. On the duplicated Column right click  the header and click 'Split Column' - 'By number of characters'

3. Type in 18, and select 'Once, as far left as possible'. Click OK. See image.

Note

The reason for duplicating the column first is so that the original column is not affected. This funuction will split the column, keep the first column and 'delete' the column with the excess letters. Unless you need this of course.

4. From the menu ribbon, select 'Add Column', and click 'Conditional Column'

5. Add your criteria here based on the new column that is already split into first 18 chars. See image.

6. Dont forget to name the column and click OK.

Hope that works, let me know how you get on.

Thanks

shebr

[shebr · Accepted solution]

Hi @BachFel

You can use the following "IF(LEFT(E0[Apfel], 18) = "Objektbesichtigung", E0[Birne], BLANK())"

Or

This can be done in the Power BI Editor.

1. Go to your editor, duplicate the [Aptel] Column, right click on the column header and click 'Duplicate column'

2. On the duplicated Column right click  the header and click 'Split Column' - 'By number of characters'

3. Type in 18, and select 'Once, as far left as possible'. Click OK. See image.

Note

The reason for duplicating the column first is so that the original column is not affected. This funuction will split the column, keep the first column and 'delete' the column with the excess letters. Unless you need this of course.

4. From the menu ribbon, select 'Add Column', and click 'Conditional Column'

5. Add your criteria here based on the new column that is already split into first 18 chars. See image.

6. Dont forget to name the column and click OK.

Hope that works, let me know how you get on.

Thanks

shebr

[Zubair_Muhammad · Accepted solution]

@BachFel

=
IF (
    LEFT ( 'LN'[Leistungsbeschreibung], 18 ) = "Objektbesichtigung",
    'LN'[Datum]
)

[BachFel]

Thanks for help, one further problem..

There can be more than one row with the text "Objektbegehung" in LN[Leistungsbeschreibung], per ID.

i.E.:

ID          Leistungsbeschreibung       Datum 

111        Objektbegehung                 20.03.2018

222        abcd                                    14.01.2018

111        Objektbegehung                 01.01.2018

I want to create a formula in another table (where all ID´s are listed once). Output should be:

ID      Datum 

111    01.01.2018

222    Blank

Onye the earliest date (per ID) should be listed. If the text is not "Leistungsbeschreibung" ist should be Blank()

Anybody who understands the problem? =/

[BachFel]

Zeitpunkt der Begehung = if('LN'[Leistungsbeschreibung]=LEFT("Objektbegehung";14);'LN'[Datum];BLANK())

This is only working if the text in LN[Leistungsbeschreibung] is exactly "Objektbegehung"

If the test is longer i.e Objektbegehung caused by reason xyz, the formula is not working.

Anybody who can help