Forum Discussion
DAX if else problems
Hi AlB,
So here is my sample:
First table hourly rate:
Key is a calculated column to later match the hourly rate with timeentries.
(1) if "travel time" equals "Ja"; Key is "Fahrtzeit"
(2) if "Task" is not empty; Key is Project + "|" + Task
(3) if "Task" is empty and "Project" is not empty; Key is Project
(4) else Key is Customer
These are like all the posible scenarios. A timeentry could be related to a travel time (1), to a task (2) or to a project (3). If no specific hourly rate is found, the standard customer rate should be matched (4).
This is my timeentries table:
You can ignore column worker. I got my desired results via multiple colums.
"hourly rate" (desired column) is "hourly rate 1" + "hourly rate 2" + "hourly rate 3"
hourly rate DEV is what i would like to have (all the above in one column). I think the problem is, that the nested if clause does not work correctly as the if clauses are subsets of each other.
so its like
IF Condition 1 THEN 1
ELSE IF Condition 2 THEN 2
ELSE IF Condition3 THEN 3
ELSE 4
My understanding is that if one row falls to condition 1 the if clause should stop even if e.g. Condition 3 would also be correct for this row. But in my DAX for "hourly rate DEV" its not working like that.
i hope that helps.
P.S.: The tables are NOT related
Anonymous
I formatted the code bit so that we see clearly the three conditions you have (in red) and the corresponding result (in green)
hourly rate DEV =
SUMX (
'Stundensätze';
IF (
C1 'Stundensätze'[Key] = 'Zeiteinträge'[Key] && 'Zeiteinträge'[Date] > 'Stundensätze'[start] && 'Zeiteinträge'[Date] < 'Stundensätze'[end];
R1 'Stundensätze'[value];
IF (
C2 'Zeiteinträge'[Project] = 'Stundensätze'[Key];
R2 'Stundensätze'[value];
IF (
C3 'Zeiteinträge'[Customer] = 'Stundensätze'[Key] && 'Zeiteinträge'[Date] > 'Stundensätze'[start] && 'Zeiteinträge'[Date] < 'Stundensätze'[end];
R3 'Stundensätze'[value]
)
)
)
)I'm quoting you:
My understanding is that if one row falls to condition 1 the if clause should stop even if e.g. Condition 3 would also be correct for this row. But in my DAX for "hourly rate DEV" its not working like that.
What do you mean exactly? Can you try to explain this again? I don't quite get it.
If the condition 1 isn't met, the first IF continues to check the second IF, since that is what you have as result for the condition not being met (specified as third parameter of the first IF). It does NOT stop.
Anonymous
Does it make sense that you have 'Stundensätze'[value] always, in all three IFs?
S, regardless of which condition is met C1, C2 or C3, you take 'Stundensätze'[value] as value to add in the SUMX?
What i meant is:
If in your formatted code bit C1 is met, the rest should stop. Like if C1 = TRUE do R1 --> check for next row. In my case its like C1 = TRUE, so do R1 --> continue checking C2. Thats why the hourly rate DEV column is wrong.
For example:
For the highlighted row C1 = false, C2 = true (it should stop here), C3 would be true aswell. If the result of "hourly rate DEV" would be calculated correctly, it should be "100", but it is "180". That can only mean it did not stop after C2 = true.
For your other question:
Yes, i always need to sum 'Stundensätze'[value]. The results in hourly rate 1 , 2 and 3 are fine.
It's like i connect the two tables 1:1 with each other, but instead of connecting via a "simple" column (like Date) i need a quite complex rule of how to connect the tables. For each row in timeentries there can always only be one correct row in hourly rate.