MDX: return last value for selected items in Power BI

[vdburg]

Hi,

This is a question regarding Cubes and MDX formulas.

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 managamente 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]

Which returns this:

I would prefer to have the november value returned at the 'All' level. But this is not my main problem. 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?

 Thanks

[v-yuta-msft]

Hi vdburg,

It seems like your issue is more related with SSAS, I would recommend you to submit your issue to SSAS forum for more support.

Regards,

Jimmy Tao