SSAS Cube Median Measures missing from PowerBI

[smithmeridian]

I am using PowerBI desktop and connecting to a SSAS tabular model cube. This is working just fine, except there are three measures missing from the list of fields.

Through experimentation, I was able to determine that any measure with a MEDIAN or MEDIANX function is not being brought into PowerBI. If I use SUM, the measure will appear. I made sure to check for hidden measures but anything with those MEDIAN functions are nowhere to be found.

These are simple measures, similar to: "Median of X:=MEDIAN([X])". 

It would appear PowerBI is filtering out Medians on purpose, but I can't figure out why. I suppose I could make my own Median measure in the PowerBI desktop, but my clients want to be able to easily grab the measure from the cube... which kind of makes sense because that's why we built the cube in the first place, right?

Any ideas on how to fix this? Any help will be greatly appreciated.

[v-shex-msft]

Hi @smithmeridian,

Based on testing, I can import median measures from live mode SSAS tabular model datasource.

Can you please provide more detail information about this issue?

Regards,

Xiaoxin Sheng

[smithmeridian]

I have tried adding the measure three different ways:

Median Measure1:=MEDIAN([Column])
Median Measure2:=MEDIAN(Table[Column])
Median MeasureX:=MEDIANX(Table, [Column])

All three appear in the measures when I load the data source to a PivotTable. They all work identical. 

I also connected to this data source in SSRS Report Builder and I am able to see all three measures.

I then connect to the datasource live in PowerBI Desktop. The measures are nowhere to be found. I can search for "median" and I receive no results. If I view hidden fields, they are still nowhere to be found.

I am using the following PowerBI Desktop version:

2.50.4859.502 64-bit (September 2017)

[smithmeridian]

I will also add that I have other aggregate measures using the same table/column that are appearing fine in Power BI.

EDIT: Our SSAS Tabular models are using SQL Server 2012 RTM (1100) compatibility level. Would this affect the measure in PowerBI?

[v-shex-msft · Accepted solution]

HI @smithmeridian,

>>Our SSAS Tabular models are using SQL Server 2012 RTM (1100) compatibility level. Would this affect the measure in PowerBI?

Based on further test and document, I found the median function only available on sql 2016 or over. Not sure how did you create the median function, I try to create but failed.

This function is included in SQL Server 2016 Analysis Services (SSAS), Microsoft Power Pivot in Excel 2016 Preview editions, and Microsoft Power BI Designer Preview only. Information provided here is subject to change.

MEDIAN Function (DAX)

Regards,

Xiaoxin Sheng