Forum Discussion

SiroPW's avatar
SiroPW
Icon for Helper I rankHelper I
7 years ago
Solved

Max & Min Dates Only Filter (top1/bottom1)

HI,

 

I have a dataset like follows:

 

DateValue
2019/08/0110
2019/08/0120
2019/08/0130
2019/08/0140
2019/08/0150
2019/07/01100
2019/07/01200
2019/07/01300
2019/06/011
2019/06/012
2019/06/013
2019/06/014
2019/05/011000
2019/05/012000

 

What I would like to do is be able to filter on the max and minimum dates so the result would be:

DateValue
2019/08/0110
2019/08/0120
2019/08/0130
2019/08/0140
2019/08/0150
2019/05/011000
2019/05/012000

 

I could achieve the same effect by manually ticking the 'Date' basic filter but I would have to change it every time the dataset updates. Similarly I could do topN filters for top and bottom dates but can't apply both at the same time AFAIK.

 

Any help is appeciated, thanks.

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi SiroPW ,

     

    The formula in your measure is missing another condition:

    Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,"Yes","No")

    I attached my pbix here for your reference, also please share yours if you are still stuck in it. 

     

6 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Icon for Solution Sage rankSolution Sage
    7 years ago

    i would use a calculated colunm to check if the date is equal to the max or min date and then filter on this colunm 

     

    example colunm below

     

    MAX&MIN =
    -- first get the max date as a veriable
    var Maxd = max('Table'[Date])
    -- next get the min date as a veriable
    var mind = min('Table'[Date])
    -- get the date in the row
    var d= 'Table'[Date]
    -- check if the date is equal to eaither of the two dates and if it is return TRUE if not RETURN FALSE
    return if(or(d = Maxd,d =mind),TRUE, FALSE)
     
     
    this colunm can then be used as a filter in the table to display only those that are true
    • SiroPW's avatar
      SiroPW
      Icon for Helper I rankHelper I
      7 years ago

      Thanks for the reply.

       

      Do you know how I could modify this to work with only the filtered/selected values?

       

      I tried ALLSELECTED like below but I'm getting an error "The MAX function only accepts a column reference as an argument".

       

      MAX&MIN =

      -- first get the max date as a veriable

      var Maxd = max(ALLSELECTED('Table'[Date]))
      -- next get the min date as a veriable
      var mind = min(ALLSELECTED('Table'[Date]))
      -- get the date in the row
      var d= 'Table'[Date]
      -- check if the date is equal to eaither of the two dates and if it is return TRUE if not RETURN FALSE
      return if(or(d = Maxd,d =mind),TRUE, FALSE)
      • v-diye-msft's avatar
        v-diye-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi SiroPW ,

         

        You can take try of this one:

        MAX&MIN = var maxd = MAXX(ALLSELECTED('Table'),[Date])
var mind = MINX(ALLSELECTED('Table'),[Date])
Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,1,0)

        When the date were filtered, it still works: