Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AUaero
Responsive Resident
Responsive Resident

Unexpected result when filtering DAX

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!

 

1 ACCEPTED SOLUTION
AUaero
Responsive Resident
Responsive Resident

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Smiley Happy

 

Best Regards
Maggie

AUaero
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.