Dear All,
I want to analyze the performance weekly and get previous week as below:
However, I don’t get the expected value because the measure below give " Total Calls " of all Dates table instead of “Calls” of selected week:
If I replace [Weekly Selection] by a number (for example 19), I get right result.
Here is my [Weekly Selection] :
Please help me. Thank you.
Jennie
Hi,
If you provide the pbix file it will be grt for form member to solve your problem here what i see here you have not put date table under all() to clr the filtration on that first .
Pls first clr filter on date and then inject your custom filter it should work.
Thanks
@Jennie ,
I did a detailed YouTube video on this topic that I think should provide you what you need to solve your problem.
Hope this is helpful.
Dear @Anurag,
I put ALL function as you suggested, but it doesn’t work:
However, if I did as below, it worked. (put all in one measure instead of two measure as above). I don’t understand the reason why?
I also watched video that @BrianJ suggested. Thank you for your great video.
Thanks,
Jennie
Hi @Jennie ,
You only need to remove filter context from that measure where you are actually want your calculation to go back and work as per your logic.
For your question when you call one measure in another measure the DAX wrap it up in calculate .
So what ever you are calling in this will for dax mean like this calculate(your measure).
And you are calling it in filter which give us table so calculate will convert your row context of table provide by filter and do context transition and give you entire table and on that table your outer calculate will work that why you seeing the sum of your number.
Note: @BrianJ correct me if anywhere i have gone wrong in above explanation
Thanks
Hi @Anurag ,
Thank you very much for your explanation. I have read it many times but I don’t really understand the DAX’s behavior on one measure in another measure with FILTER(). The measure “Weekly Selection” returns a specific value. You mean in my case, filter context is overwritten, isn’t it ? and is why I see sum of all Dates table ?
If possible, could you please explain more clearly ?
Thank you.
Jennie
Hi @Jennie -
What I understand is by using a measure [Weekly Selection] inside FILTER function, instead of returning value from the Selected Filter i.e. 20, it is Reevaluating [Weekly Selection] for each Row of Filter(all(Dates)) by passing Week Number as a Filter (This is as measure has a Calculate by default and Calculate converts Row Context into Filter Context) and then doing the comparison.
As it is Reevaluating [Weekly Selection] for each Row of Date, Dates[Week Number] = [Weekly Selection] will always match. Hence we get output as “Total Calls” of all Dates.
If it is not clear, then share a sample PBIX file and I may be able to explain better.
Thanks
Ankit J
@Jennie You are not getting the correct result because Weekly Selection measure performs context transition when called in the row context of FILTER ( Dates…
Each row of the dates table is converted into a filter context, and then filters your IF ( HASONEVALUE () … code.
Every time the current row of the dates table is converted into a filter context, the VALUES and MAX are going to return the week number visible in the filter context created by Context Transition, in practice only 1 week number which the week number currently iterated by FILTER.
So for each row your code says 1 = 1, 2 = 2, 3 = 3 etc. That’s why you get everything and not only for the selected week.
To fix this calculation create a seprate variable that uses SELECTEDVALUE ( Dates[Week Number] ) that will retrieve the value of week number from filter context that is outside of CALCULATE.
Hi @ankit , @AntrikshSharma
Now I understand clearly. Thank you so much for this detailed explanation.
Thanks,
Jennie
