MDX: return last value for selected items in Power BI

Question

This is a question regarding SSAS Cubes, MDX formulas and Power BI.

I have a measure with the active members per each month. So when I select for example 2018 it shouldn´t aggregate but return the last available month with active members, and if I break down by month it should give the active members for each month.

So I have this formula which works almost fine if querying in MS Management Studio:

with member [Measures].[Last existing SOCIOS] AS 
Max(
EXISTING [DIM FECHA].[Jerarquía].[MES NOMBRE].members,
iif([Measures].[ACTIVOS] = 0,null,
[Measures].[ACTIVOS])
)

 select {[Measures].[Last existing SOCIOS]} on columns,
[DIM FECHA].[MES NOMBRE].members on rows
from [cubo_Compromisos]
where [DIM FECHA].[AÑO].&[2018]

I would prefer to have the november value returned at the 'All' level. But this is not my main problem. The real issue is that when I use this measure in Power BI it behaves differently: when selecting multiple months it ignores the selected values and just returns the last value for the whole year.

In the screenshot below I have added the value returned by the KPI Card because that is the value that I want returned:

If I select items like this it does it right, but I need it to select all months, and not just one because I am using this measure along others:

Does anyone know the right MDX function to use or an alternative?

Edited: 23-11-2018

It does the same in a Pivot Table connected to a SSAS Cube.When I add the date dimension to the table it works fine. But when using the date dimension and filtering it without the dimension added as rows it returns the value for the whole year.

Veselin Davidov Veselin Davidov · Accepted Answer · 2018-11-20T09:59:21

The function you are looking at is LastChild. Last Child on the upper level of the hierarchy will return the value you are looking at.

I think that function can be used in the Cube design in SSAS - then this will be the standard behavior. If you want to do it with a query you need to do something like:

SELECT [Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2002].LastChild ON 0
FROM [Adventure Works]

To get the last month of the 1st quater (I used example from microsoft and another post on the subject )

MDX: return last value for selected items in Power BI

1 Answers