Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX if else problems

Hello,   I have the following situations. I got 2 tables. table one contains time entries and table 2 contains the hourly rate which a customer has to pay. There are several conditions which impact...
Need Help
Tips and Tricks
AlB's avatar
AlB
Community Champion
7 years ago

Hi Anonymous

 

Is there any relationship between your tables 'hourly rate'  and  'timeentry'? You should show more info on your model, ideally share the pbix.

In any case, the SUMX is run on the 'hourly rate' table but you are trying to access  timeentry[Key]. If this is a column, you have no row context for table timeentry in this expression and thus the code should yield an error. I am surprised it doesn't.  I'm guessing the problem with the condition not behaving as you expect will be related to that.        

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 1 = SUMX('Stundensätze'; IF('Stundensätze'[Key] = 'Zeiteinträge'[Key] && 'Zeiteinträge'[Date] > 'Stundensätze'[start] && 'Zeiteinträge'[Date] < 'Stundensätze'[end]; 'Stundensätze'[value]))
hourly rate 2 = SUMX('Stundensätze'; IF(ISBLANK('Zeiteinträge'[hourly rate 1]) && 'Zeiteinträge'[Project] = 'Stundensätze'[Key]; 'Stundensätze'[value]))
hourly rate 3 = SUMX('Stundensätze'; IF(ISBLANK('Zeiteinträge'[hourly rate 1]) && ISBLANK('Zeiteinträge'[hourly rate 2]) && 'Zeiteinträge'[Customer] = 'Stundensätze'[Key] && 'Zeiteinträge'[Date] > 'Stundensätze'[start] && 'Zeiteinträge'[Date] < 'Stundensätze'[end]; 'Stundensätze'[value]))

 

hourly rate DEV = SUMX('Stundensätze'; IF('Stundensätze'[Key] = 'Zeiteinträge'[Key] && 'Zeiteinträge'[Date] > 'Stundensätze'[start] && 'Zeiteinträge'[Date] < 'Stundensätze'[end]; 'Stundensätze'[value]; IF('Zeiteinträge'[Project] = 'Stundensätze'[Key]; 'Stundensätze'[value];IF('Zeiteinträge'[Customer] = 'Stundensätze'[Key] && 'Zeiteinträge'[Date] > 'Stundensätze'[start] && 'Zeiteinträge'[Date] < 'Stundensätze'[end]; 'Stundensätze'[value]))))

 

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