# Medians and context issues in DAX



## srizzo123 (Nov 22, 2013)

I am having an issue that I am hoping some more experienced  DAX programmers may be able to help me with. This problem is cross-posted on StackOverflow here (excel - Medians and slicers in DAX - Stack Overflow) with no answers so far. 

I have been trying to  develop a dashboard in Excel 2013 / PowerPivot / PowerView and one of  the graphics I would like to display is a line chart of median  performance by hour of day. I would then like to filter the data set  with my performance metrics based on a separate field, and link that to a  slicer. The medians should be calculated relative to the filtered data  set. For the median calculation I am trying to adapt the formula  proposed by Marco Russo here (SQLBI - Marco Russo : Median calculation in DAX).

  To illustrate the problem, suppose that I have two tables - *main_table* and *other_table*. *Main_table* has 4 fields - *RowID*, *hour_of_day*, *performance_metric*, and *category*. *Other_table* has two fields - *hour_of_day* and *median_field*. My goal is to find a formula for *median_field* such that it shows the median performance metric by hour of day, but can still be sliced by *category*. The formula I tried to use for the medians was


```
<code>=CALCULATE(MINX(FILTER(VALUES(main_table[performance_metric]), CALCULATE(COUNTA(main_table[performance_metric]), main_table[performance_metric] <= EARLIER(main_table[performance_metric])) > COUNTA(main_table[performance_metric]/2), main_table[performance_metric]), FILTER(main_table, main_table[hour_of_day] = EARLIER(other_table[hour_of_day]))) </code>
```

However, when I create a slicer based on *category* in *main_table*, my chart does not seem affected by the slicer. My understanding was that by putting *main_table* as opposed to *ALL(main_table)* as the first argument in the last FILTER call, my median calculations would be subject to slices and filters applied to *main_table*. Am I missing something obvious here?


----------



## marco.russo (Nov 22, 2013)

I took a look at the formula, but it's not clear to me whether it's a measure (calculated field) or a calculated column. If it's in a measure (calculated field) it's not clear to me what you are doing with "other table" in the EARLIER statement. If you can post a sample repro workbook it would be simpler to take a look at the formula.


----------



## srizzo123 (Nov 22, 2013)

Sorry - I tend to mix up calculated fields and calculated columns. The formula is for the calculated column *median_field* in *other_table*. I will update the original post with a link to a sample file.


----------



## srizzo123 (Nov 22, 2013)

I apologize for double posting, but I seem to be unable to edit my own posts for some reason (mods - please feel free to merge this into the post above). Anyways, the download link for the sample file is

median_and_context_issue.xlsx - Speedy Share - upload your files here

I can upload it elsewhere if that would be more convenient.


----------



## marco.russo (Nov 30, 2013)

You cannot use a calculated column in this case. Calculated columns are computed at process time and store the result into the Power Pivot table *before* you do any query.
If you want your calculation to be affected by the slicer, you have to create a measure (aka calculated field in Excel 2013).
You should do this in two steps:
1) Create the relationship between the two tables using the HOD column
2) Create the Median calculated field using the following DAX code:


```
Median :=
MINX (
    FILTER (
        VALUES ( main_table[Performance_metric] ),
        CALCULATE (
            COUNTROWS ( main_table )
            main_table[Performance_metric]
                <= EARLIER ( main_table[Performance_metric] )
        )
            > COUNTROWS ( main_table ) / 2
    ),
    main_table[Performance_metric]
)
```

Let me know if this works for you.


----------



## srizzo123 (Dec 2, 2013)

That does work - the measure changes when I adjust the slicer. Unfortunately I will need a lot of measures - in the actual application I want to plot the medians of at least 5 different performance metrics by hour of day (that can still be sliced by other categories) - but if calculated columns are pre-computed then measures are really my only option. Thanks for the help!


----------



## Tom_Poland (Aug 11, 2017)

Hello Guys!

I need to create a pivot with median (MS Office Pro 2010). I tried to define a measure with code indicated above, however I get a pop up, that syntax of calculate is incorrect. Could you please help me? I haven't changed anything in the code but table and column names.


----------



## Tom_Poland (Aug 14, 2017)

Anyone..?


----------

