Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Lori001
Frequent Visitor

DAX code result changes based on column position. How to fix this?

Hi community,

 

I have a Dax code challenge and don't know how to fix this.

 

I have a table where I want to calculate the date difference between the columns StartDate and EndDate.

The result should be the difference in days.

If there is no EndDate, the result should be the date difference between StartDate and Date today.

 

The dax formula that I used looks like this:

 

DurationDays = DATEDIFF('Car'[StartDate],IF(ISBLANK(RELATED('Paper'[EndDate)),TODAY(),RELATED('Paper'[EndDate])), DAY)

 

This should be the correct formula, but depending on the position where I add the produced dax column DurationDays, I get different results:

 

In this example, I get the correct table rows, but I'm missing the diference in days when EndDate is not filled in:

 

IDStartDateAmountTotal amountEndDateDurationDays

11-1-2023    
21-1-2023    
31-1-2023    
41-1-2023700140015-2-202345
51-1-2023700140022-2-202352
61-1-2023    
71-1-2023    
81-1-2023    
91-1-202330070015-2-202345
101-1-202330070016-2-202345
111-1-2023    
121-1-2023    
131-1-202310030015-2-202345

 

 

In example two, I change the position of DurationDays to the end of the table visualization and then I see the difference in days of the empty EndDate according the dax code.

 

The problem now is that I see two rows:

- One row which takes the difference between StartDate and filled in EndDate. I always want to see this when EndDate has a value.

- One row which takes the difference between StartDate and empty EndDate. In only want to see this when EndDate has no value.

 

IDStartDateAmountTotal amountDurationDaysEndDate

11-1-2023  85 
21-1-2023  85 
31-1-2023  85 
41-1-202370014004515-2-2023
41-1-2023  85 
51-1-202370014005222-2-2023
51-1-2023  85 
61-1-2023  85 
71-1-2023  85 
81-1-2023  85 
91-1-20233007004515-2-2023
91-1-2023  85 
101-1-20233007004516-2-2023
101-1-2023  85 
111-1-2023  85 
121-1-2023  85 
131-1-20231003004515-2-2023
131-1-2023  85 

 

What I would like to see is below example:

One table with each ID occuring only once. 

 

IDStartDateAmountTotal amountEndDateDurationDays

11-1-2023   85
21-1-2023   85
31-1-2023   85
41-1-2023700140015-2-202345
51-1-2023700140022-2-202352
61-1-2023   85
71-1-2023   85
81-1-2023   85
91-1-202330070015-2-202345
101-1-202330070016-2-202345
111-1-2023   85
121-1-2023   85
131-1-202310030015-2-202345

 

Unfortunately I don't seem to get this work.

 

Thank you for your help.

 

1 ACCEPTED SOLUTION

Hi binbinyu,

 

Apoligies for my late reply. Didn't see the notification and I also thought it was marked as spam.

How I solved the matter was by shifting the columns accordingly. Basically I changed the orders and that allowed me to see what I wanted to see. 

 

My table had more columns so cannot show it here, but I guess its clear how I solved the matter.

 

Thank you for your response anyway.

View solution in original post

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

Hi @Lori001 ,

This problem may be caused by the data model.

Could you please provide desensitized example data? It is very helpful for me to test.

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_Binbin Yu

Hi binbinyu,

 

Apoligies for my late reply. Didn't see the notification and I also thought it was marked as spam.

How I solved the matter was by shifting the columns accordingly. Basically I changed the orders and that allowed me to see what I wanted to see. 

 

My table had more columns so cannot show it here, but I guess its clear how I solved the matter.

 

Thank you for your response anyway.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors