Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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!
Hi @Dellis81
Great that helped. Can you please mark that post as Solution!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |