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?

Neuberger

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 ? – Midgett

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. – Neuberger

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!

Midgett

Hi @HugoSalaun, very clever approach with the dynamic set. I still have some problems but we are very very close. First, this CInt([Time].[DATE].MEMBER_KEY) produces a type mismatch error in PBI. Second, this CInt(Year([Time].[DATE].MEMBER_KEY)) is able to get the correct year, but this one CInt(Day([Time].[DATE].MEMBER_KEY)) always gives the last date of each month ou have the slider and not the max date on the slider. I dont get it. – Neuberger

Hi again @HugoSalaun, after a lot of investigation, seems that maybe is my issue as it doesn't work with member_key but it works for memver_value. So this code Day(MAX(EXISTING setDate, [Time].[DATE].MEMBER_VALUE)) is working perfect for me! Thanks a lot! – Neuberger

Indeed, the CInt function wasn't a good idea, in my model I used integers to go faster. I edited my answer and edited the measure. Note that my "Key0" attribute is sourced from a DateTime column, is it the same for you ? – Midgett

Oops, didn't see your last comment, I'm glad it's working with MEMBER_VALUE for you. As I mentionned before, it's really sensitive to how you designed your model, for me using MEMBER_VALUE was catastrophic but I could do it with Properties('Key0', Typed) ... well, at least it works ! – Midgett