topic Re: Using ALLCROSSFILTERED with CALCULATE in DAX Commands and Tips

Using ALLCROSSFILTERED with CALCULATE

RandomJoe — Thu, 23 May 2019 20:16:58 GMT

According to the documentation (https://docs.microsoft.com/en-us/dax/allcrossfiltered-function-dax) I should be able to use ALLCROSSFILTERED within a CALCULATE, but I'm getting an error that it failed to resolve name 'ALLCROSSFILTERED' in the following measure:

Count All Employees = CALCULATE(SUM('Employee Dim'[Custodian Count]), ALLCROSSFILTERED('Employee Dim'))

Can someone help me here?

In the end, what I am trying to do is select the name of the person from the Employee dimension for the USERNAME() that is logged in, so I need to remove all the filters that may have been applied. The ALL() function only does it for filters applied directly to that table and not for any related tables.

Re: Using ALLCROSSFILTERED with CALCULATE

RandomJoe — Fri, 24 May 2019 15:21:05 GMT

For others who may have this question, I have a partial solution:

Logged In User = CALCULATE(SELECTEDVALUE('Employee Dim'[Custodian Name]),FILTER(ALL('Employee Dim'),[Custodian Email]=USERPRINCIPALNAME()),ALL('Connected Client Fact'))

By using a second filter on the central Fact table I was able to remove any crossfiltering that was occuring on the Employee dimension.

You'll note that this solution is predecated on the point that there is a central table. This would not have worked if I needed data from the Fact table. If that were the case I would need to remove all the filters individually from each connected dimension.

I hope an expert can tell me why ALLCROSSFILTERED is not working.

Re: Using ALLCROSSFILTERED with CALCULATE

marcushenningse — Wed, 02 Oct 2019 06:05:16 GMT

I'm experiencing the same issue. One assumption of mine is that it has to do with the version of the SSAS which has not been updated with the latest cumulative updates and thus not supports the newest DAX functions listed here: https://docs.microsoft.com/en-us/dax/new-dax-functions