CODEWITHSUNDEEP
if-statementfilterpowerbidaxslicers
Leniel Maccaferri
Leniel Maccaferri

Reputation: 102448

PowerBI query doesn't work with date filter

I have this DAX:

Earned Daily =
VAR d = SELECTEDVALUE ( 'P6 Update'[Date] )
RETURN
IF (
    'Data for Pivot'[Date] <= d
        && 'Data for Pivot'[Act Rem] = "Actual Units"
        && 'Data for Pivot'[Type] = "Current",
    'Data for Pivot'[Value],
    0
)

'P6 Update'[Date] is attached to a Slicer (single select date dropdown).

I see that the date is being correctly set but when I try to use the date to filter it doesn't work.

If I do this:

Earned Daily =
IF (
    'Data for Pivot'[Date] <= DATE ( 2018, 4, 19 )
        && 'Data for Pivot'[Act Rem] = "Actual Units"
        && 'Data for Pivot'[Type] = "Current",
    'Data for Pivot'[Value],
    0
)

The data is filtered correctly.

What's the problem with the original code?

#######

Note: P6 Update table is created with this code:

P6 Update = CALENDAR(MIN('Data for Pivot'[Date]), MAX('Data for Pivot'[Date]))

Upvotes: 1

Views: 956

Answers (2)

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102448

Based on @Alexis Olson's answer, I put together this one that does exactly what I want:

Earned Daily = 
VAR d = SELECTEDVALUE ( 'P6 Update'[Date] )
RETURN
CALCULATE (
    SUM('Data for Pivot'[Value] ),
    FILTER(ALLSELECTED('Data for Pivot'),'Data for Pivot'[Date]=MAX('Data for Pivot'[Date])),
    'Data for Pivot'[Date] <= d,
    'Data for Pivot'[Act Rem] = "Actual Units",
    'Data for Pivot'[Type] = "Current"
)

Added FILTER which makes it match Date by Date until 'P6 Update'[Date] is reached.

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

Your DAX formula appears to be a calculated column rather than a measure. Since calculated columns are only evaluated once each time the table is loaded, they cannot be responsive to slicers or dynamic filtering of any sort.

The solution is to write a measure to produce the values you want instead. It would look something like this:

Earned Daily =
VAR d = SELECTEDVALUE ( 'P6 Update'[Date] )
RETURN
CALCULATE (
    SUM ( 'Data for Pivot'[Value] ),
    'Data for Pivot'[Date] <= d,
    'Data for Pivot'[Act Rem] = "Actual Units",
    'Data for Pivot'[Type] = "Current"
)

You might need to make adjustments depending on what context you are trying to use this measure in, but this is the basic approach.

Upvotes: 1

Related Questions