Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
Best Regards
Maggie
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |