left(Tabelle1[ColumnA]=

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?

Need Help

shebr
8 years ago
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

8 years ago

BachFel

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

4 Replies

BachFel
Helper II
8 years ago
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
shebr
Resolver III
8 years ago
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
  Community Champion
  8 years ago
  BachFel
  
  = IF ( LEFT ( 'LN'[Leistungsbeschreibung], 18 ) = "Objektbesichtigung", 'LN'[Datum] )
  - BachFel
    Helper II
    8 years ago
    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? =/

Forum Discussion

left(Tabelle1[ColumnA]=