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?

I can't reproduce your issue right now but it might be because the generated query use a subcube when you switch to range-slicer. The result is that you apply MAX on the member [All] of a previously-filtered cube and it might explains why you get 0. Can you try to run a Provider and show us the complete query ? — Hugo Salaun
Hi, @HugoSalaun imagine that these exact queries are my problem, if I manage to have the date from slicer I can continue working on bigger queries. So in order to reproduce it you need one Time dimension, one Measure Group with at least one measure and one of these calculated members. — Stavros Koureas

Hugo Salaun Hugo Salaun · Accepted Answer · 2019-08-18T19:16:11

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!

MDX Get Min Max Date From Slicer Element

1 Answers