How to multiply a specific value in one column by -1 depeding on text from another column?

[Anonymous]

Hi,

Some cells in the column "Kommentar" include cells with texts like this: 04.10.2022 - ÜÜ  or this:  24.10.2022 - ÜÜN
Dates may vary, important in this case are only ÜÜ and ÜÜN. Almost every other cell in the column "Kommentar" is blank.

Some cells in the column "Grund Abwesend" include cells with the text "Feiertag".

The numbers of the column "Einzeldauer" must be copied to the column "Ersatz f Abw" as long as the text of the cell in column "Grund Abwesend" includes "Feiertag" and the cell in the column "Kommentar" inludes "ÜÜ".

Further to that:

The numbers of the column "Einzeldauer" must be multiplied by -1 and copied to the column "Ersatz f Abw" as long as the text of the cell in column "Grund Abwesend" includes "Feiertag" and the cell in the column "Kommentar" inludes "ÜÜN".

If both conditions do not apply then the corresponding cells of the column "Ersatz f Abw" should show "null".

Any ideas how to solve that? Thank you in advance!

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

Ersatz f Abw =
VAR GrundAbwesend = 'Table'[Grund Abwesend]
VAR Kommentar = 'Table'[Kommentar]
VAR Einzeldauer = 'Table'[Einzeldauer]
RETURN
    IF (
        GrundAbwesend = "Feiertag"
            && CONTAINSSTRING ( Kommentar, "ÜÜ" ),
        - Einzeldauer
    )

[TheBiConsultant]

This is not really a DAX question, since this problem should be resolved in the data model. You can either do this in Power Query inside Power bi desktop / dataflows (ask in the Power Query forum section) or if possible, in your database before you load data into Power Bi.