Very interesting video. thanks a lot . i think the following code using offset gives the same result (and should be a bit optimal ): VAR F= OFFSET(-1,CALCULATETABLE(SUMMARIZE(Sales,'Date'[Date]),ALLEXCEPT('Date','Date'[Day of Week]))) VAR R1=calculate([Sales Amount],F) return if(NOT(ISBLANK([Sales Amount])),R1)
Nice video! Good to know the impacts of using the Date column of the date table when playing with the filter context within Calculate. However, I do have a question: within the DAX code, you're getting the MAX(date) from the Sales table, as well as removing filters from it, i.e., applying filters/calculates directly on the fact table. Would this trigger Auto-Exist somehow and impact on final result?
The MAX is used on Sales to get the last transaction with dates. Applying the filter on Date or on the Fact table depends on specific results that should be achieved. Please, use the comments on SQLBI website (see link in the description) to get more technical answers, thanks!
If the goal is to use only the dates in the visual, yes - but if you are creating a measure that should be used in other contexts, it could not work well.
That works only when you select a day of the week ( you reput the filter taken away by REMOVEFILTERS ) but if you remove the selection, then it give a not correct result because it imposes a filter you do not want
This seems unsatisfying to me. It requires the measure code to "guess" what Calendar table slicers the user will use in any random report. The second the user puts a slicer for Fiscal Month, say, you have to change the ALLEXCEPT code to accommodate. Seems like there should be some way to express the concept "all except current slicers" :D
You cannot "know" what is the current slicer, as a slicer is not an entity in DAX. You can guess by using ISFILTERED, but it's a guess... there are no differences of filters produced by filter pane, slicers, or rows/columns of a matrix. Only ISINSCOPE can discriminate them, but ISINSCOPE works only at the column level and not at the table level (as ISFILTERED can do).
Very interesting video. thanks a lot . i think the following code using offset gives the same result (and should be a bit optimal ):
VAR F= OFFSET(-1,CALCULATETABLE(SUMMARIZE(Sales,'Date'[Date]),ALLEXCEPT('Date','Date'[Day of Week])))
VAR R1=calculate([Sales Amount],F)
return if(NOT(ISBLANK([Sales Amount])),R1)
Thanks Marco. Could you please explain or show [Day of week short]?
Nice video! Good to know the impacts of using the Date column of the date table when playing with the filter context within Calculate.
However, I do have a question: within the DAX code, you're getting the MAX(date) from the Sales table, as well as removing filters from it, i.e., applying filters/calculates directly on the fact table. Would this trigger Auto-Exist somehow and impact on final result?
The MAX is used on Sales to get the last transaction with dates. Applying the filter on Date or on the Fact table depends on specific results that should be achieved. Please, use the comments on SQLBI website (see link in the description) to get more technical answers, thanks!
Thank you Marco. Would replacing the hidden ALL ( ‘Date’[Date] ) with ALLSELECTED ( ‘Date’[Date] ) work?
If the goal is to use only the dates in the visual, yes - but if you are creating a measure that should be used in other contexts, it could not work well.
What about re-adding the filter with VALUES(Date[date]) to remove the effect of the hidden REMOVEFILTERS?
That works only when you select a day of the week ( you reput the filter taken away by REMOVEFILTERS ) but if you remove the selection, then it give a not correct result because it imposes a filter you do not want
This seems unsatisfying to me. It requires the measure code to "guess" what Calendar table slicers the user will use in any random report. The second the user puts a slicer for Fiscal Month, say, you have to change the ALLEXCEPT code to accommodate. Seems like there should be some way to express the concept "all except current slicers" :D
You cannot "know" what is the current slicer, as a slicer is not an entity in DAX. You can guess by using ISFILTERED, but it's a guess... there are no differences of filters produced by filter pane, slicers, or rows/columns of a matrix. Only ISINSCOPE can discriminate them, but ISINSCOPE works only at the column level and not at the table level (as ISFILTERED can do).
@@SQLBI (sigh)