Dax Statement If all selected then ...... else if filtered then .......

[KarenFingerhut]

Hi there

Im trying to create some DAX and am struggling. 

I have a specific slicer at the top that can be sliced by sub account numbers. What I want to say in my statement is if all the sub account numbers are selected then filter on a specific flag and return a sum with that filter against it other wise sum up value without that filter if any of the sub accounts are selected. So essentially something like this

IF(ALLSELECTED(Income[Sub Account Code],CALCULATE(SUMX(FILTER(Income,Income[consolidated_flag]=1),Income[balance]))),IF(NOT(ALLSELECTED(Income[Sub Account Code],SUM(Income[balance]))))

The above is wrong but that kind of statement

Hope this makes sense, any help would be really appreciated

Thank yu

Karen

[Anonymous · Accepted solution]

Without any sample data or table structure, field names, etc... it is difficult to give a DAX code to solve it. But I will try to explain what mistake you are making and how you can correct it.

1) ALLSELECTED() is not used to determine whether all the items in a field are selected or not. Its use is similar to the ALL function that removes all filters from a table. But there is a critical difference between ALL() and ALLSELECTED(). Before explaining the difference, you need to understand the basics of Evaluation Contexts. All formulas are calculated in a particular evaluation context and an evaluation context comprises of a Filter Context and a Row Context.

The same formula, when used in a visual, for example, a matrix visual, will give different results in each row typically and it is because although the formula is the same, it is evaluated in a particular filter context and row context.

Filter contexts are introduced into the calculation using things like slicers, page filters, visual filters, report level filters, RLS, and so on.

Row Context is introduced based on the current record (based on the aggregation level, it could include the records down the hierarchy also).

What ALL() does is that it removes both Filter Context and Row Context from the calculation and calculates the result without applying any filter. In contrast, ALLSELECTED() will retain the filter context and remove only the row context. 

I am not sure if you have understood the difference, but explaining this in detail will take a longer post and already there are online resources at the Microsoft site as well as on sites like SQLBI.com. You must read and understand this because understanding the evaluation context is very very very critical for anyone using DAX.

In your scenario, if you want to check if all sub-accounts have been selected or not and then based on that you want to perform a different calculation, you could use something like the following condition.

VAR CountOfSubAccounts = COUNTROWS(ALL(Income[Sub Account Code]))
VAR CountofSelectedSubAccounts = COUNTROWS(ALLSELECTED(Income[Sub Account Code]))
VAR Result = If(CountofSubAccounts = CountofSelectedSubAccounts,<calculation1>,<calculation2>)
Return Result

[amitchandak]

@KarenFingerhut , I think you need isfiltered or isinscope 

IF(isfiltered(Income[Sub Account Code],CALCULATE(SUMX(FILTER(Income,Income[consolidated_flag]=1),Income[balance]))),IF(NOT(isfiltered(Income[Sub Account Code])),SUM(Income[balance])))

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://xxlbi.com/blog/new-dax-function-isinscope/