All filter

[Dellis81]

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! 

[pranit828 · Accepted solution]

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]

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!

[pranit828]

Hi @Dellis81 

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

Regards,

Pranit

[Dellis81]

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!

[az38]

Hi @Dellis81 

maybe 

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

 ?