cancel
Showing results 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.

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

 1 1-1-2023 2 1-1-2023 3 1-1-2023 4 1-1-2023 700 1400 15-2-2023 45 5 1-1-2023 700 1400 22-2-2023 52 6 1-1-2023 7 1-1-2023 8 1-1-2023 9 1-1-2023 300 700 15-2-2023 45 10 1-1-2023 300 700 16-2-2023 45 11 1-1-2023 12 1-1-2023 13 1-1-2023 100 300 15-2-2023 45

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

 1 1-1-2023 85 2 1-1-2023 85 3 1-1-2023 85 4 1-1-2023 700 1400 45 15-2-2023 4 1-1-2023 85 5 1-1-2023 700 1400 52 22-2-2023 5 1-1-2023 85 6 1-1-2023 85 7 1-1-2023 85 8 1-1-2023 85 9 1-1-2023 300 700 45 15-2-2023 9 1-1-2023 85 10 1-1-2023 300 700 45 16-2-2023 10 1-1-2023 85 11 1-1-2023 85 12 1-1-2023 85 13 1-1-2023 100 300 45 15-2-2023 13 1-1-2023 85

What I would like to see is below example:

One table with each ID occuring only once.

IDStartDateAmountTotal amountEndDateDurationDays

 1 1-1-2023 85 2 1-1-2023 85 3 1-1-2023 85 4 1-1-2023 700 1400 15-2-2023 45 5 1-1-2023 700 1400 22-2-2023 52 6 1-1-2023 85 7 1-1-2023 85 8 1-1-2023 85 9 1-1-2023 300 700 15-2-2023 45 10 1-1-2023 300 700 16-2-2023 45 11 1-1-2023 85 12 1-1-2023 85 13 1-1-2023 100 300 15-2-2023 45

Unfortunately I don't seem to get this work.

1 ACCEPTED SOLUTION
Frequent Visitor

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.

2 REPLIES 2
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.

Best regards,
Community Support Team_Binbin Yu

Frequent Visitor

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.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors