topic Assistance Need w DAX Calculation to Replace Blanks with the Value from Another Column in DAX Commands and Tips

Assistance Need w DAX Calculation to Replace Blanks with the Value from Another Column

Anonymous — Thu, 28 Oct 2021 23:38:22 GMT

I have a table in which I am calculatiing a countdown of days from the start of an event to find the average days to address the event. Once an event is addressed, a finished date is entered and the event completion duration can be calculated. If an event hasn't been addressed, the days continue to accumulate in the calculation. What I need is a measure that will divide the number of days an event is open divided by the overall count of records to get an average. But if an event isn't closed, I need the measure to return a value I have calculated in another column which is counting the number of days between event start and today. I have tried to use an IF statement to replace a blank with the field from the table but it won't work. Example:

Average Days for Event = IF(DIVIDE([Event Completion Duration], [Overall Event Count], [Running Days]

When an event hasn't been addressed, it shows as blank in the Event Completion Duration column. That's why I want it to then pull the value from the Running Days column but only in the case of a blank.

I am new to DAX and this has been the most complicated thing I've tried to address so far.

Re: Assistance Need w DAX Calculation to Replace Blanks with the Value from Another Column

udhayakumarr28 — Fri, 29 Oct 2021 03:40:02 GMT

Hi Anonymous, better you need to provide Sample Data and Expected results in an image so that we can easily understand your question.

Re: Assistance Need w DAX Calculation to Replace Blanks with the Value from Another Column

Anonymous — Fri, 29 Oct 2021 05:11:18 GMT

udhayakumarr28,

An example would be as follows:

Event ABC was created on 1/1/21 and completed on 1/5/21 (4 day duration)
Event DEF was created on 1/1/21 and completed on 1/10/21 (9 day duration)
Event GHI was created on 1/15/21 and has yet to be completed (Running total)
Event JKL was created on 2/1/21 and completed on 2/9/21 (8 day duration)

There are 4 total events with an average depending on the value from event GHI. I have a measure that calculates the number of records overall and I have measures to calculate event duration and another return the running total based on today(). What I can't do is put them all together to give me the average days for completion including the event that has yet to be completed. I can calculate the average number of days for completed events but I can't get further than that.

Does that make more sense?

Re: Assistance Need w DAX Calculation to Replace Blanks with the Value from Another Column

v-yanjiang-msft — Wed, 03 Nov 2021 06:28:21 GMT

Hi Anonymous ,

According to your description, I create a sample and you can try like this.

1.Create the column “Event Completion Duration”.

2.Create the measure ”Average Days for Event”.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.