Unexpected result when filtering DAX

[AUaero]

Hi,

I've got a problem I can't seem to get.  I'm sure it's a result of my not understanding how the filters are being propagated through the DAX code, so if someone here can help, I'd really appreciate it.

I've got a calculated measure that calculated the median number of unique customers in the previous quarter for the entire population of sales people in our organization:

VAR SummaryTable = 
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            ALL(Order_Taken_By),
            Order_Taken_By[USER_NAME] <> BLANK() &&
            Order_Taken_By[JOB_TITLE] = "Sales Person"
        ),
        Order_Taken_By[USER_NAME]
    ),
    "Value", 
    CALCULATE(
        Customers[UNIQUE_CUSTOMERS],
        FILTER(
            ALL('Calendar'), 
            'Calendar'[PREVIOUS_QTR_FLAG] = "PREVIOUS QUARTER"
        )
    )
)

RETURN MEDIANX(SummaryTable, [Value])

If I create a table in Power BI with sales person, unique customers, and the median unique customers, I get the result I expect.

Sales Person	Unique Customers	Median Unique Customers
Jim	63	57
Bob	51	57
Suzy	28	57
Mark	68	57
Alex	42	57
Tony	67	57

The issue comes in when I try to filter the table by manager.  The sales person field is from a table that looks something like:

Sales Person	Manger
Jim	Stephen
Bob	Stephen
Suzy	Stephen
Mark	Stephen
Alex	Sandra
Tony	Sandra

As soon as I apply the manager filter, no value is returned by my median calculation.

By watching the SQL profiler when Power BI executes the query, I see that it is creating a filter variable as :

VAR __DS0FilterTable3 =
TREATAS({"Stephen"}, Order_Taken_By[MANAGER_NAME])

What am I missing here?  I thought that by including ALL(Order_Taken_By) as the table argument in the SUMMARIZE function of the measure, I would tell DAX to ignore any additional filters that are being passed.  Any help you guys can provide would be great!

Thanks!

[v-juanli-msft]

Best Regards
Maggie

[AUaero · Accepted solution]

So, I found that if create another measure and use ALL() to modify the median measure, I get the expected result.

Here's the original median measure:

MedianMeasure = 
VAR SummaryTable = 
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            ALL(Order_Taken_By),
            Order_Taken_By[USER_NAME] <> BLANK() &&
            Order_Taken_By[JOB_TITLE] = "Sales Person"
        ),
        Order_Taken_By[USER_NAME]
    ),
    "Value", 
    CALCULATE(
        Customers[UNIQUE_CUSTOMERS],
        FILTER(
            ALL('Calendar'), 
            'Calendar'[PREVIOUS_QTR_FLAG] = "PREVIOUS QUARTER"
        )
    )
)

RETURN MEDIANX(SummaryTable, [Value])

If I create another measure like so, it works:

MedianTest =
[MedianMeasure](ALL(Order_Taken_by))

So it seems I ought to be ab able to put the ALL() argument in the second measure somewhere in the first, right?  I'm just having trouble figuring out where it goes.