Unexpected result when filtering DAX

Question

Hi,&nbsp;I've got a problem I can't seem to get.&nbsp; 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.&nbsp;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:&nbsp;VAR SummaryTable =&nbsp;ADDCOLUMNS(&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL(Order_Taken_By),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order_Taken_By[USER_NAME] &lt;&gt; BLANK() &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order_Taken_By[JOB_TITLE] = "Sales Person"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order_Taken_By[USER_NAME]&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Value",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Customers[UNIQUE_CUSTOMERS],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL('Calendar'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[PREVIOUS_QTR_FLAG] = "PREVIOUS QUARTER"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))RETURN MEDIANX(SummaryTable, [Value])&nbsp;&nbsp;If I create a table in Power BI with sales person, unique customers, and the median unique customers, I get the result I expect.&nbsp;Sales PersonUnique CustomersMedian Unique CustomersJim6357Bob5157Suzy2857Mark6857Alex4257Tony6757&nbsp;The issue comes in when I try to filter the table by manager.&nbsp; The sales person field is from a table that looks something like:Sales PersonMangerJimStephenBobStephenSuzyStephenMarkStephenAlexSandraTonySandra&nbsp;As soon as I apply the manager filter, no value is returned by my median calculation.&nbsp;By watching the SQL profiler when Power BI executes the query, I see that it is creating a filter variable as :&nbsp;VAR __DS0FilterTable3 =
TREATAS({"Stephen"}, Order_Taken_By[MANAGER_NAME])&nbsp;&nbsp;What am I missing here?&nbsp; 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.&nbsp; Any help you guys can provide would be great!&nbsp;Thanks!&nbsp;

auaero · Accepted Answer

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] &lt;&gt; BLANK() &amp;&amp;
            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?&nbsp; I'm just having trouble figuring out where it goes.

Forum Discussion

Unexpected result when filtering DAX

2 Replies

Related content

Unexpected error: Arithmetic operation resulted in an overflow.

Rank Issue - Table vs Filter Results

Unexpected Calculate/Filter Results

SWITCH produces unexpected value if results only differ by case

Unexpected Infinity

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

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