Forum Discussion

Dellis81's avatar
Dellis81
Post Prodigy
6 years ago
Solved

All filter

Hello - I suspect there is a easy solution - that is not coming to me right away.

 

VAR YMinValue = MINX(VALUES('CALENDAR'[Year]),CALCULATE([ItemPurchaseDollars]))

 

I have a year slicer on the canvas - and would like the above measure to return the min value across all years within the datamodel.

 

I know I need to be using the "All" filter - but not finding correct combination of where it fits in.

 

thanks! 

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Dellis81 

     

    I believe what you are looking for is

     

    CALCULATE(MIN([ItemPurchaseDollars]), ALLEXCEPT('CALENDAR'[Year]))
    (OR)
    CALCULATE(MINX([ItemPurchaseDollars]), ALLEXCEPT('CALENDAR'[Year]))

     

    Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

     

    Regards,
    Pranit

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Dellis81 

     

    I believe what you are looking for is

     

    CALCULATE(MIN([ItemPurchaseDollars]), ALLEXCEPT('CALENDAR'[Year]))
    (OR)
    CALCULATE(MINX([ItemPurchaseDollars]), ALLEXCEPT('CALENDAR'[Year]))

     

    Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

     

    Regards,
    Pranit

     

    • Dellis81's avatar
      Dellis81
      Post Prodigy

      Thank you guys for responding - I probably didn't explain well enough.    I am trying to incorporate sparklines into my matrix visuals - and found the attached file.   It works well, when comparing months.   However, I was trying to tweak the dax formulas so we would have sparkline by year.

       

      Sparkline Line x Yr = 
      // Static line color
      VAR LineColor = "#01B8AA"
      // "Date" field used in this example along the X axis
      VAR XMinDate = MIN('Table'[Date])
      VAR XMaxDate = MAX('Table'[Date])
      // Obtain overall min and overall max measure values when evaluated for each date
      VAR YMinValue = MINX(VALUES('Table'[Date]),CALCULATE([Measure Value]))
      VAR YMaxValue = MAXX(VALUES('Table'[Date]),CALCULATE([Measure Value]))
      // Build table of X & Y coordinates and fit to 100 x 100 viewbox
      VAR SparklineTable = ADDCOLUMNS(
          SUMMARIZE('Table','Table'[Date]),
              "X",INT(100 * DIVIDE('Table'[Date] - XMinDate, XMaxDate - XMinDate)),
              "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
      // Concatenate X & Y coordinates to build the sparkline
      VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", [Date])
      // Add to SVG, and verify Data Category is set to Image URL for this measure
      VAR SVGImageURL = IF(HASONEVALUE('Table'[Category]),
          "data:image/svg+xml;utf8," & 
          "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
           "<polyline fill='none' stroke='" & LineColor & 
           "' stroke-width='3' points='" & Lines & 
           "'/></svg>",
           BLANK())
      RETURN SVGImageURL

       I've attached the original sparkline file and on page 1 - is my start of what i need.   A year slicer that displays that year's measure, and then a sparkline across all years.

       

      I know this is a little more than I orginally asked - but getting this sample file to you was easier than piecing something from my larger file.  

      File link

      https://1drv.ms/u/s!AmBVCme14p7xlV_uC0f_E0payT3N

       

      Anything with a Table.date connotation - will need to be flipped to a year connotation.  Thanks again!

       

    • Dellis81's avatar
      Dellis81
      Post Prodigy

      Thank you very much!   After more review this morning - was able to make this particular calculation work.   Your second option is what I needed.

       

      What I am trying to do - is convert a dailer sparkline measure into an annual measure.   And this was one of the calculations inside the larger measure.    I am still havig problems - but will open another case - as my original hangup is now fixed.

       

      Thank you again!

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi Dellis81 

         

        Great that helped. Can you please mark that post as Solution!

         

        Regards,

        Pranit

  • az38's avatar
    az38
    Community Champion

    Hi Dellis81 

    maybe 

    CALCULATE(MIN([ItemPurchaseDollars]), ALLSELECTED('CALENDAR'[Year]))

     ?