Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dellis81
Continued Contributor
Continued Contributor

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! 

1 ACCEPTED SOLUTION
pranit828
Community Champion
Community Champion

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

 





PBI_SuperUser_Rank@1x.png


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

View solution in original post

5 REPLIES 5
pranit828
Community Champion
Community Champion

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

 





PBI_SuperUser_Rank@1x.png


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
Dellis81
Continued Contributor
Continued Contributor

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





PBI_SuperUser_Rank@1x.png


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
Dellis81
Continued Contributor
Continued Contributor

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
Community Champion
Community Champion

Hi @Dellis81 

maybe 

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

 ?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors