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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
tamiribas
Resolver I
Resolver I

Return Min/Max Year following a DimDate[Year] Slicer

When Selecting a few years in the slicer, I need to get the minYear and maxYear
tamiribas_1-1700408658977.png

The expected values in the table are:2022,2024


I am trying the following code but it only gives me the absolute min/max years, irrespective of the selection in the slicer.

 (tried with MINX and MAX to see if there is a difference)

 

EVALUATE
VAR _minYear = MINX(ALLSELECTED(dimDate[Year]),dimDate[Year])
VAR _maxYear = MAX(dimDate[Year])
RETURN
{COMBINEVALUES(",",_minYear,_maxYear)}
 
Thank you
Tamir
1 ACCEPTED SOLUTION

Hey @tamiribas ,

 

basically, you can not achieve the layout of the table that you are looking for, at least not with the default Matrix table visualization.

The reason for this is because it's not possible to use different columns on the same row or column, here it's the min/max years and two calcualtions.

 

You can use custom visuals, e.g. the inforiver table visual. You will find this visual in the app store:

 

What can be achieved more or less simple is to only keep the first (min) and the last (max) selected year from rows or columns. You can achieve this by creating a measure that you then use as visual level filter.

This is the measure that filters out the in-between selected years:

 

vizAid FilterForMinMax = 
var currentYear = SELECTEDVALUE( dimDate[Year] )
var _t = ALLSELECTED( 'dimDate'[Year] )
var YearSelectedMin = MINX( _t,  'DimDate'[Year] )
var YearSelectedMax = MAXX( _t,  'DimDate'[Year] )
var minmaxyeartable = 
    UNION(
        ROW( "Year" , YearSelectedMin)
        , ROW( "Year" , YearSelectedMax)
    )
return
IF( currentYear in minmaxyeartable, 1 , BLANK())

 

The image below shows how this measure is used on the matrix visual and a sample Matrix visual:

image.png

Consider creating two new measures that make use of the min/max years, and use these measures on rows instead, even if there will be no values for the first year.

 

Hopefully, this helps to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

10 REPLIES 10
tamiribas
Resolver I
Resolver I

Hi @TomMartens 

Although what you suggested works, it does not work when I use it in my final formula.

 

The expected values for the Years should be 2023 and 2025

tamiribas_0-1700452336059.png

 

Summerized Table Min/Max Years = 
var _t = VALUES( 'dimDate'[Year] )
 var YearSelectedMin = MINX( _t,  'DimDate'[Year] )
 var YearSelectedMax = MAXX( _t,  'DimDate'[Year] )

VAR _SELECTEDYEARS = 
	SUMMARIZECOLUMNS(
		dimDate[Year],
        TREATAS({YearSelectedMin, YearSelectedMax}, dimDate[Year]),
		"SalesAmount", [SalesAmount],
		"TotalAmount", [TotalAmount]
	)
RETURN
    UNION
    (
    _SELECTEDYEARS,
    ROW("Year", "Yoy", "SalesAmount", [SalesAmount],"TotalAmount", [TotalAmount])
    )

 Appreciating your help,

Tamir

Hey @tamiribas ,

 

pleaese provide a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link.

 

Please describe the expected result based on the sample data you provided.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens 

Here is a link to the pbix file: https://drive.google.com/file/d/1YcgL7Xwi8xTY2VhJdOmyia9zgYYI3uwN/view?usp=sharing 

 

Appreciating your time and willingness to help,

 

Regards,

Tamir

 

Hey @tamiribas ,

 

I miss 

"Please describe the expected result based on the sample data you provided."

 

You have to be aware that you can not create a virtual table that "reacts" on the selection of a user in a given slicer. Virtual tables will only be calcualted during dataset refresh, but not when a user is selecting different years on a slicer. This is the same as the execution of calculated columns.

 

So, you have to think in measures that omit years that are not the min year or the max year.

I'm wondering what you want to be calculated for YoY as well?

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens ,

I wrote the expected results in the pbix.

However, I believe you have pinpointed my problem. I am trying to update a calculated table with selected values of a slicer. I understand now that this is not possible, because the calculated table will only be refresh upon a refresh of the report.

 

In that case, I am asking your help to redesign my attempt for the solution.

 

The business use case is to show the following table:

YearMin Selected Year (e.g. 2022)Max SelectedY ear (e.g. 2025)difference between the selected yearsdivision of the selected years
Measure: [TotalSales]    
Measure: [Count Transactions]    
Measure: [Average per Transaction]    

 

Thank you kindly,

Tamir

Hey @tamiribas ,

 

adding a table visual is not what I'm looking for when I ask for: Please describe the expected result based on the sample data you provided.

Next, now you are using "transaction", is a transaction a single row of the fact table?
Please describe the expected result based on the sample data you provided.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens ,

The expected results based on the sample data are displayed in the following screenshots (done in Excel):

tamiribas_1-1700567550825.png

 

If I choose different Years in the Slicer, e.g. 2022 and 2006 I will see those years in the column title and their respective outcome.

 

Hope that it is clearer.

 

If not, kindly point out what is not clear.

 

Thank you,

Tamir

Hey @tamiribas ,

 

basically, you can not achieve the layout of the table that you are looking for, at least not with the default Matrix table visualization.

The reason for this is because it's not possible to use different columns on the same row or column, here it's the min/max years and two calcualtions.

 

You can use custom visuals, e.g. the inforiver table visual. You will find this visual in the app store:

 

What can be achieved more or less simple is to only keep the first (min) and the last (max) selected year from rows or columns. You can achieve this by creating a measure that you then use as visual level filter.

This is the measure that filters out the in-between selected years:

 

vizAid FilterForMinMax = 
var currentYear = SELECTEDVALUE( dimDate[Year] )
var _t = ALLSELECTED( 'dimDate'[Year] )
var YearSelectedMin = MINX( _t,  'DimDate'[Year] )
var YearSelectedMax = MAXX( _t,  'DimDate'[Year] )
var minmaxyeartable = 
    UNION(
        ROW( "Year" , YearSelectedMin)
        , ROW( "Year" , YearSelectedMax)
    )
return
IF( currentYear in minmaxyeartable, 1 , BLANK())

 

The image below shows how this measure is used on the matrix visual and a sample Matrix visual:

image.png

Consider creating two new measures that make use of the min/max years, and use these measures on rows instead, even if there will be no values for the first year.

 

Hopefully, this helps to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens 

 

Thank you for the suggested solution.

 

Your measure indeed helps with showing only Min/Max Years. Using this and utilizing the "Total" column I can get closer to my desired goal ( I assign a specific measure to be shown in the place of the Total column).

I will try the inforeaver visual (however...I don't see the exact name "inforeaver table".

 

Appreciating very much your time and willingness to assist,

 

Kind regards,

Tamir

TomMartens
Super User
Super User

Hey @tamiribas ,

 

this DAX measure returns what you are looking for:

 

selected Calendar Years = 
 var _t = VALUES( 'DimDate'[CalendarYear] )
 var YearSelectedMin = MINX( _t,  'DimDate'[CalendarYear] )
 var YearSelectedMax = MAXX( _t,  'DimDate'[CalendarYear] )
 return
COMBINEVALUES("," , YearSelectedMin , YearSelectedMax)
 

 

Here is a simple visualization of the selection and the result:
image.png

Using ALLSELECTED returns all the years because it removes the innermost filter on the column in question. For a slicer visual, the innermost filter is the selection, hence it always returns the first and last year. VALUES returns a table of the selected or filtered values of the column uses as a parameter. Here you will find more details about VALUES: https://dax.guide/values/

Next, you use MAX without CALCULATE, for this reason the existing filter context given by the selection is not considered.

I'm wondering about the expected result if only one year is selected?

 

Hopefully, this helps to tackle your challenge.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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