I’m trying to get the min value of a slicer selection in a row of a matrix, even if there is no data for that slicer selection/row combination. The idea is that I want to compare the values from the min and max month, but only if the min and max months match the min and max slicer selections.
Sample .pbix below. This example does not use a separate date table, but I could create one with that if needed.
Sample Report.pbix (20.8 KB)
@DeanJ That’s a feature of DAX Engine known as Auto Exists, Store 1 doesn’t have a row for Month = 1 that’s why the engine can’t return 1 for you when you try to remove filter from Month column.
So even if you think you are removing filter from Month using ALLSELECTED/ALL/REMOVEFILTERS/ALLEXCEPT the engine is smart and will consider only those combinations of Store and Month that exists in the data.
So you will have to change the calculation to consider the whole table offcourse that will have some wrong calculations as well, the best way to solve this issue is to model your data into fact and dimensions using star schema approach, so that Auto Exists only work on a single dimension and doesn’t affect other tables naturally.
MinALLSelectedMonth =
CALCULATE ( MIN ( Sheet1[Month] ), ALLSELECTED ( Sheet1 ) )
@AntrikshSharma Thank you for your reply! I had not heard about Auto Exists, but it makes sense.
If I am doing the modelling I always use a separate date table, but sometimes I am asked to create Power BI reports for existing models which I can’t change.