Remove visual filter context on dates but keep slicer filters on

user_273506 0
Viewed 96

I have two tables like this

enter image description here

I am trying to get NAV of the first date (in the selected dates). I tried multiple ways but couldn't get it. Here are a couple of expressions I tried. Any help would be greatly appreciated.

NAV First Date = CALCULATE(MIN(FundNAV[NAV]),ALLEXCEPT(FundNAV,FundNAV[Fund Name]),FIRSTDATE(Dates[Date]))


NAV First Date = CALCULATE(MIN(FundNAV[NAV]),FILTER(ALL(Dates[Date]),Dates[Date]=MIN(Dates[Date])))

enter image description here

I got it working with the below measure, but have a bug in the expression. As you can see for the third fund there is no record on the selected first date, so it's returning blank, ideally, it should return the next available NAV for all remaining days.

NAV First Date = VAR frist_date = CALCULATE ( FIRSTDATE( Dates[Date] ),ALLSELECTED(FundNAV),VALUES(FundNAV[Fund Name]))
        RETURN CALCULATE(MIN(FundNAV[NAV]),Dates[Date]=frist_date)

enter image description here

dax powerbi
2 Answers

Try this:

NAV First Date =
VAR mindate =
    CALCULATE ( MIN ( FundNAV[NAV Date] ), ALLSELECTED ( Dates[Date] ) )
RETURN
    CALCULATE ( MIN ( FundNAV[NAV] ), ALL(FundNAV[NAV Date]), FundNAV[NAV Date] = mindate )

This measure first calculated the minimum FundNAV[NAV Date], within the selected dates from the Dates tabel. Then it returns the FundNAV[NAV] for that date.

user_62304070

Please test below 2 measures. I think Both will solve your problems:

NAV First Date =
CALCULATE ( MIN ( FundNAV[NAV] ), ALL ( FundNAV[Fund Name]), ALL ( Dates[Date] ) )

and This one uses the entire fact table as filter. (Expanded tables logic. it also removes any filter on dates)

NAV First Date =
CALCULATE ( MIN ( FundNAV[NAV] ), ALL ( FundNAV ) )
user_194690880
Related