How to use filter when creating calculated column in Power BI?

0
votes

Filter cannot be used in calculated columns, then how can I simply create a column in DAX that would pick up a value based on maximum claimID and maximum DateCreated ?

enter image description here

The result should be like this:

enter image description here

2
powerbidaxpowerbi-desktop
Why 100? It doesn't appear to correspond to the latest DateCreated? You only take nonzero and there aren't further nonzero values? – Alexis Olson

2 Answers

3
votes

Filters can be used in calculated columns. Just don't expect them to be responsive to slicers. In this case, it is probably more efficient computationally not to use a FILTER function in favor of simple CALCULATE arguments:

TotalIncurredMaxDate =
VAR MaxDate =
    CALCULATE (
        MAX ( Table1[DateCreated] ),
        ALLEXCEPT ( Table1, Table1[ControlNo], Table1[ClaimID] ),
        Table1[TotalIncurred] > 0
    )
RETURN
    IF ( Table1[DateCreated] = MaxDate, Table1[TotalIncurred], 0 )
1
votes

Calculated columns have a "row context" of the current row, and no filter context, as they are calculated during model load, before any filters are applied. But you can create filters in your calculation. EG

TotalIncurredMaxDate = 
    var claimId = Table1[ClaimID]
    var controlNo = Table1[ControlNo]

    var maxDate = calculate(
        max(Table1[DateCreated]), 
        filter (
          all (Table1), 
          Table1[ClaimID]=claimId
          && Table1[ControlNo] = controlNo
          && Table1[TotalIncurred] > 0
        )
    )

    var retval = if(Table1[DateCreated] = maxDate, Table1[TotalIncurred], 0)

    return retval