Forum Discussion

sdgiss's avatar
sdgiss
Helper I
5 years ago
Solved

Maintaining filter context after applying ALL function

First off, let me state that I am very new to DAX. The question I have relates to maintaining filter context after applying the ALL function to create a new measure.

The new measure identifies the all-time (ALL dDates) best max speed for each bike racer. When reporting daily max speed on a specific date, there are records for those who did not record a value on that date (but did over the previous year). I realize this is probably a simple solution but I am struggling to have both the date filter context in the pivot table and the all-time best measure for only those racers that recorded a max speed on that specific date. I would only like to report an all-time best max speed for those that had a max speed record on a specific date. My pivot table is below with a specific example where Racer 9 & 10 have no data for 10/21 but still show up because they have an all-time max speed. The DAX formulae are as follows:

Max Speed All Dates = CALCULATE ( MAX ( fData[Max Speed] )FILTER ( ALL ( dDate ), dDate[Date] ) )

Max Speed per Racer =CALCULATE(MAX(fDataReference[Max Speed]),ALLEXCEPT(dRacerDetail,dRacerDetail[Racer]))

 

Thank you!

 

Steve

 

  • sdgiss  just adding a if function in your measure

    Max Speed All Dates = IF([Daily Max],CALCULATE ( MAX ( fData[Max Speed] ), FILTER ( ALL ( dDate ), dDate[Date] ) )))

6 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    sdgiss  just adding a if function in your measure

    Max Speed All Dates = IF([Daily Max],CALCULATE ( MAX ( fData[Max Speed] ), FILTER ( ALL ( dDate ), dDate[Date] ) )))
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion

    Hi, amitchandak , Anonymous , wdx223_Daniel , a quick question please: can you guys shed some light on this expression?

    FILTER ( ALL ( dDate ), dDate[Date] )

    what's the differece between it and ALL ( dDate )? Thanks in advance!

    • sdgiss's avatar
      sdgiss
      Helper I

      CNENFRNL there is no need for the use of FILTER in this formula, as ALL is more than enough to achieve the result. My immature DAX brain was overcompensating with FILTER when writing this measure.

  • sdgiss , Try like

    Max Speed All Dates = CALCULATE ( MAX ( fData[Max Speed] ), FILTER ( ALL ( dDate ), dDate[Date] ) ,not(isblank(fData[Max Speed] )) )

  • Anonymous's avatar
    Anonymous
    Not applicable

    sdgiss   Hey Mate ,

    You can try this .

     

    Max Speed All Dates = if(HASONEVALUE(dDate[Date]),
                                         CALCULATE ( MAX ( fData[Max Speed] )FILTER ( ALL ( dDate ), dDate[Date] ) ),Blank() )

     


    Max Speed per Racer =if(HASONEVALUE(dRacerDetail[Racer]), CALCULATE(MAX(fDataReference[Max Speed] ),Blank() )

    Try this and let me know
    You can use this as well.