ALL function on related table not clearing filters in CALCULATE

[jeffreyweir]

Hi folks. I've got a simple data model that looks like below. 

The YearsService Table only exists so that I can force things to display in a particular order.

In a Table Visual, I've got the YearsService in the column far left, and I'm using the following measure to count how many folk are in each tenure bucket: 

HeadCount (Perm) =
CALCULATE (
    SUM ( CTE[EmpCount] ),
    CTE[EmpStatus] <> "Terminated",
    CTE[EmpType] = "Perm"
)

...and I was wanting to calculate how many folk in all buckets, so that I could then calculate a percentage in each:

HeadCount All (Perm) =
CALCULATE (
    SUM ( CTE[EmpCount] ),
    CTE[EmpStatus] <> "Terminated",
    CTE[EmpType] = "Perm",
    ALL ( YearsService[YearsService] )
)

...but that ALL doesn't seem to get transmitted to the CTE table, as per the below visual:

What I expected is that the second column would have the number 493988 in each cell. 

What am I missing here?

[OwenAuger · Accepted solution]

Hi @jeffreyweir

It looks like you've run into the side effects of the "sort by column" feature.

See this article:

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

In short, when you have a visual containing a filter on the "sorted" column, the "sort by" column is also added to the filter context , so you need to clear filters on both if you want to effectively remove the effect of the sorted column.

In your case I would recommend removing filters on the entire YearsService table, by changing your measure to:

HeadCount All (Perm) =
CALCULATE (
    SUM ( CTE[EmpCount] ),
    CTE[EmpStatus] <> "Terminated",
    CTE[EmpType] = "Perm",
    ALL ( YearsService )
)

Regards,

Owen

[jeffreyweir]

Ahhh! Good to know. Thanks Owen. Hopefully see you at the next Difinity so I can buy you a beer in gratitude.