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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dbrandone
Helper III
Helper III

ALLSELECTED help for use with multiple values

Hi Everyone,

 

I have a report that has button filters for years. I have a gauge visual grouping with one gauge showing the current year selected and the second gauge showing the previous year to the year selected. Below is the current dax measures for the gauge visuals now. Right now I have single select on, but if the user holds ctrl and selects multiple years, then the visuals show errors. I have been trying to think of a way where 2 years could be selected and the year prior to the Minimum selected would show in the 2nd gauge. I tried different things but haven't found a solutions yet. I wanted to pose the question to everyone here and see what ideas people may have. Any ideas or help would be appreciated.

 

Year Selected =
VAR StartDate = DATE(ALLSELECTED('Date'[Year]), 1, 1)

VAR EndDate = DATE(ALLSELECTED('Date'[Year]), 12, 31)

VAR Result = CALCULATE(
                 DIVIDE([Transplant Tissue Totals], [Transplant Rate Denominator]),
                 DATESBETWEEN('Date'[Date], StartDate, EndDate)
    )

    Return Result
Previous Year of Selected =
VAR StartDate = DATE(ALLSELECTED('Date'[Year])-1, 1, 1)

VAR EndDate = DATE(ALLSELECTED('Date'[Year])-1, 12, 31)

VAR Result = CALCULATE(
                 DIVIDE([Transplant Tissue Totals], [Transplant Rate Denominator]),
                 DATESBETWEEN('Date'[Date], StartDate, EndDate)
                 )

Return Result

 

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@dbrandone  You can change your date to use MIN:

 

VAR StartDate = DATE(MIN('Date'[Year])-1, 1, 1)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

2 REPLIES 2
AllisonKennedy
Super User
Super User

@dbrandone  You can change your date to use MIN:

 

VAR StartDate = DATE(MIN('Date'[Year])-1, 1, 1)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thanks @AllisonKennedy

That works for the previous year measure, but with the first measure if ALLSELECTED is still used, it throws an error. If I use MAX, then only the max year of the two selected years shows in the data, but I was able to use your premise with MIN and MAX to get the currently selected year visual to display properly. Thanks!! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.