MDX Get Min Max Date From Slicer Element

Question

I am using PowerBI Desktop/Service as a Front-End and Analysis Services Multidimensional as Back-End. I want to create a complex calculated member but I have a problem in some part of the member.

For example, I need to have the MIN/MAX DATE of the TIME dimension which is used in the filter-element-slicer.

I have already tried some simply calculated members to start:

First One:

MAX
(
   EXISTING
   [Time].[DATE].Members,
   [Time].MEMBER_KEY
)

Second One:

MAX
(
   NONEMPTY
   [Time].[DATE].Members,
   [Time].MEMBER_KEY
)

Third One:

MAX
(
   EXISTING
   [Time].[YQM].[DATE].Members,
   [Time].[YQM].MEMBER_KEY
)

Fourth One:

MAX
(
   NONEMPTY
   [Time].[YQM].[DATE].Members,
   [Time].[YQM].MEMBER_KEY
)

I also have tried different by using a measure inside like this one:

MAX
(
 (
  [Time].[DATE].Members,
  [Measures].[Sales Amount]
 ),
[Time].[DATE].Member_Key
)

All of them are showing the last DATE of TIME dimension and not the MAX selected in the filter-element-slider of PowerBI. Any ideas?

The strange thing here is that if I use the filter-element as simple-selection-drop-down and not as range-slicer, then, I am able to retrieve the current selected date with this code:

MAX
(
   EXISTING
   [Time].[YQM].CURRENTMEMBER,
   [Time].[YQM].MEMBER_KEY
)

But when I switch to range-slicer the same code, returns zero(0)!

In addition, I have already done this using DAX in different Tabular Model and working properly, but it seems too hard for Multidimensional Model

Maybe the problem is due to the range?

Answer 1

As you mentionned, the issue isn't from PowerBI but from the query to the MultiDim model.

You can reproduce it by browsing your calculated member on Excel and filtering on the date. If you take only one date, the member refresh correctly and display this date, but if you select multiple dates, it displays "31" because "31" is the MAX of all your members. I tried to correct that behavior with a DYNAMIC SET :

CREATE DYNAMIC SET setDate AS 
{
   NONEMPTY([Time].[DATE].Members)
};

Then, I use this set to calculate my measure :

CREATE MEMBER CURRENTCUBE.[Measures].[MAX_DATE]
AS MAX(
   setDate,
   CDate([Time].[DATE].Properties('Key0', Typed)
),
VISIBLE = 1; 

(Note the CDate function, I use it to convert my MEMBER_KEY to an Date so 1/31/2020 > 9/9/2000 if you use US format, but you might not need it depending of your model.)

In Excel, my new measure works correctly even if I select multiple dates. I'm a newbie in PowerBI but I created a report and it seems to be alright.

I hope it helps!