DAX to set BLANK() or zero on unwanted rows

[TickboxPhil]

DAX experts - how best to set a value to zero if lookup condition not met? Here we have two Problem IDs which have Assignments associated - each Assignment has a Task with Planning Days calculated as difference between Assessement and Start Date:

Issue is, Planning Days should only be calculated on the first Task and subsequent Tasks should be ignored. So the first Task 28 Planning Days is correct on AssigNo 4004, but AssigTask 2 and 3 should be set to BLANK() (or at least zero) so they don't inflate the total average - which above should be (1+28)/2=14.5 not (1+28+28+28)/4=21.25!

I could do this in SQL and feed into PowerBI, but would be better to acheive it in DAX where more re-usable. An attempt is:

Planning Days Assig =
IF (
    VALUES ( v_ProbAssig[AssigTask] ) <> MIN ( v_ProbAssig[AssigTask] ),
    BLANK (),
    AVERAGE ( v_ProbAssig[PlanningDays] )
)

But this returns error that "a table of multiple values was supplied where a single value was expected" - this is fair enough because although VALUES would be auto converted to scalar on the rows, the total will have the multiple values causing the error. We could trap that with HASONEVALUE and CALCULATE the MIN AssigTask:

Planning Days TRY = 
IF (
    HASONEVALUE(v_ProbAssig[AssigTask]), 
        IF( 
            VALUES (v_ProbAssig[AssigTask]) <> CALCULATE( MIN(v_ProbAssig[AssigTask]), ALL(v_ProbAssig[AssigTask]) ), 
            BLANK (), 
            AVERAGE(v_ProbAssig[PlanningDays])
            ),
        AVERAGE(v_ProbAssig[PlanningDays])
   )

... and that almost does do the whole job... but the total is still out as need some kind of self-reference:

Can't have a blank total average as that's the whole point of calculation, so how can we fix this - or is there a better way?

TIA,

Phil

[Anonymous · Accepted solution]

Hi @TickboxPhil

1. Create a new column with Min_Assigntask=MIN(Min_Assigntask).

2. Create new Measure with this formula:

PlanningDaysAvg = CALCULATE(AVERAGE(Table1[PlanningDays]),FILTER(Table1,Table1[Assigntask]=Table1[Min_Assigntask]))

Thanks

Raj

[TickboxPhil]

Excellent @Anonymous, this solves with this data, well done:

Simplifying with the FILTER does the job on both rows and totals dynamically (I tried that previously but it wasn't aligned properly so wasn't sure if workable).

However, sorry to unmark as the answer for now, but as mentioned at the time, best not to use the Calculated Column for any MIN because it always returns against whole table (eg always Task 1 here) due to physical row context, but if you create it as another Measure instead, it will correctly return the MIN within the context of the query (eg Task 2 if the particular Assignment had a deleted or cancelled Task 1). I have done that now and added extra data to test (deleted/cancelled the first Task so AssigNo 40014 has a first Min Task of 2 not 1), which shows the problem:

I've tried to solve this using these 2 Measures:

Min Task = CALCULATE(
    MIN(v_ProbAssig[AssigTask]), 
    ALL(v_ProbAssig[AssigTask]), 
    ALL(v_ProbAssig[AssigFinishDt])
    )

Planning Days Avg = 
    VAR MinTask = v_ProbAssig[Min Task] RETURN 
    CALCULATE(
        AVERAGE(v_ProbAssig[PlanningDays]), 
        FILTER(v_ProbAssig, v_ProbAssig[AssigTask] = MinTask)
        )

We have to use the VAR (or EARLIER) to capture the original context, otherwise the comparison fails. This does work as per screenshot... apart from the critical Planning Days Avg Total, which should be the average of 28 here, not 1! This is key, so can't just blank it out, as its the value shown on visualisations elsewhere (but better to illustrate on pivot).

It would also be nice to avoid having multiple ALL filters for each column name (especially becasue there are many additional ones) as this is inflexible - so the solution could be very different. Here's a link to this workbook for anyone if it helps:

ProblemAssig PowerBI DAX

Hopefully somebody may be able to apply the best DAX to solve this,

Many thanks,

Phil