Forum Discussion
Condition ($ <>) in the expression
I have a measure and need to apply it a filter condition when the User filter a month the value of the measure need to be maintained even if the value used in the measure has no relationship with the month table, for example I have the following expression in QlikView: sum ({$ <Month =>} Vl_ReceitaPrevista)
In power bi I have sum (Vl_ReceitaPrevista).
How would I do this in power bi?
It sounds like you need an ALL or ALLEXCEPT clause in your SUM calculation. Generally, you do this by wrapping the SUM in a CALCULATE like:
CALCULATE(SUM(VI_ReceitaPrevista),ALL(table))
Basically, the ALL and ALLEXCEPT clauses create an context for the SUM that is independent of other contextual filters that may be applied, like from slicers. Can't really get too specific without sample data.
6 Replies
- Greg_Deckler
Community Champion
It sounds like you need an ALL or ALLEXCEPT clause in your SUM calculation. Generally, you do this by wrapping the SUM in a CALCULATE like:
CALCULATE(SUM(VI_ReceitaPrevista),ALL(table))
Basically, the ALL and ALLEXCEPT clauses create an context for the SUM that is independent of other contextual filters that may be applied, like from slicers. Can't really get too specific without sample data.
That was exactly what I needed, thank you
Did a test here after I applied the solution, calcaulate function (all ()) solves the problem of being presented zero when filtered dimension in my case a month dimension however when it is filtered a month that there was change and the value is not changed, for example, in January there was no movement and so when filtered it is presented to zero and should be shown the total value of the year with the function all this problem was solved however when I filter the month of February there was drive the still displayed value is the total of the year and in fact should be presented the value of the current year in February.
Briefly function cancels all the filter made of the size I expecificar the expression, in this case how could I fix this?
Follow my expression:
Budgeted Current = CALCULATE (if (ISBLANK (Sum (Facts [Budgeted Initial]) + Sum (Facts [Vl_Suplementado]) - Sum (Facts [Vl_Reduzido])); 0; (Sum (Facts [Budgeted Initial]) + Sum (Facts [Vl_Suplementado]) - Sum (Facts [Vl_Reduzido]))), ALL (DMEGF_Mes [Month]))
- Greg_Deckler
Try changing your ALL to ALLEXCEPT, see documentation here:
https://msdn.microsoft.com/en-us/library/ee634795.aspx