Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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)
Solved! Go to 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:
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
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
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
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
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:
Year | Min Selected Year (e.g. 2022) | Max SelectedY ear (e.g. 2025) | difference between the selected years | division 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
Hi @TomMartens ,
The expected results based on the sample data are displayed in the following screenshots (done in Excel):
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:
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
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
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:
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
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
177 | |
85 | |
70 | |
63 | |
55 |