Forum Discussion
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!
- Anonymous6 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
- AnonymousNot 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- Dellis81Post 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 SVGImageURLI'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!
- Dellis81Post 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!
- AnonymousNot applicable
- az38Community Champion