CODEWITHSUNDEEP
powerbidax
Arjun
Arjun

Reputation: 1069

Remove visual filter context on dates but keep slicer filters on

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

Upvotes: 1

Views: 1053

Answers (2)

Marco Vos
Marco Vos

Reputation: 2968

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.

Upvotes: 1

Ozan Sen
Ozan Sen

Reputation: 2615

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 ) )

Upvotes: 0

Related Questions