cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DPDS94 Regular Visitor
Regular Visitor

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 which hourly rate should be matched to which timeentry. For example there are hourly rates for certain projects or for certain tasks. Yet there is like a "fall back". If there is no specific hourly rate for a project, the "standard" hourly rate of the customer should be used.

 

to but it simply i have an if clause that states

SUMX( hourly rate; IF (timeentry[Key] = hourly rate[Key]; hourly rate[value]; IF( timeentry[Customer] = hourly rate[Customer]; hourly rate[value])))

So for my understanding the second if should only be executed if the first if is wrong. Yet what i experience is, that the second if is executed everytime.

What i would like to have is that the second if clause is just executed if the first one does not get any results (thats how an if should work anyway)

 

Can someone help me please?

 

Regards

Dennis

9 REPLIES 9
Super User
Super User

Re: DAX if else problems

Hi @DPDS94

 

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.        

DPDS94 Regular Visitor
Regular Visitor

Re: DAX if else problems

Hi @AlB,

 

So here is my sample:

First table hourly rate:

hourly rate.PNG

 

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:

time entries.PNG

 

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

 

 

 

 

Super User
Super User

Re: DAX if else problems

@DPDS94

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.  

Super User
Super User

Re: DAX if else problems

@DPDS94

 

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?

 

DPDS94 Regular Visitor
Regular Visitor

Re: DAX if else problems

@AlB

 

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.

 

highlight.PNG

 

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.

Super User
Super User

Re: DAX if else problems

@DPDS94

 

would it be possible to share the pbix? with dummy data if necessary

DPDS94 Regular Visitor
Regular Visitor

Re: DAX if else problems

@AlB

How can i share a link here?

Super User
Super User

Re: DAX if else problems

@DPDS94

You can post the URL to the file on platforms like Dropbox, OneDrive, etc. or you can upload the file to sites like this (no sign-in required) and post the URL here

Community Support Team
Community Support Team

Re: DAX if else problems

@DPDS94,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/DAX-Grouping-tagging/m-p/189391#M83354

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 422 members 4,099 guests
Please welcome our newest community members: