MDX script needed

Question

Hi Experts,

I need a MDX script for a calculated member in my cube.

Requirement:

The count of "legal entities" where the “FirstShipmentOverride” is populated.

Additionally, the “first shipment override” should be filtered by using the time dimension.

For example in the chart below:

• Customer A.S.T.I. is not counted --> FirstShipmentOverride is null
• Customer Aalbers Wico is counted as 1 --> FirstShipmentOverride date is 2020-08-16 and the filter in the report is set to Time.Year = 2020
• Customer Adilanti is not counted --> FirstShipmentOverride date is 2018-08-07 and the filter in the report is set to Time.Year = 2020

I tried the following .. but it is not working for condition 3 for Adilanti.

CREATE MEMBER CURRENTCUBE.[Measures].[Nr of Reactivated Customer Il]
AS
CASE
WHEN [Customer].[First Shipment Override].CURRENTMEMBER.MEMBERVALUE = [Customer].[First Shipment Override].&[1899-12-30T00:00:00] -- Blank First shipment Override
THEN
0
ELSE
Count
(Filter
(Existing([Customer].[Legal Entity]),
[Time].[Month - Year].CurrentMember = [First Override Date].[Month - Year].CurrentMember.MEMBERVALUE
)
)
END,
ISIBLE = 1 , DISPLAY_FOLDER = 'KPI' , ASSOCIATED_MEASURE_GROUP = 'Invoices';

Answer 1

Consider creating a measure group off the Customer dimension, joining it to the Time dimension on the first shipment override field.

Answer 2

Hi,

Did your screenshot is your desire result ? In the screenshot, the Adilanti is not counted and has blank as measure result.

Looks like you haven't specify the 3rd condition in your MDX query , you could modify the query as this :

CREATE MEMBER CURRENTCUBE.[Measures].[Nr of Reactivated Customer Il]  
AS  
CASE  
WHEN ([Customer].[First Shipment Override].CURRENTMEMBER.MEMBERVALUE = [Customer].[First Shipment Override].&[1899-12-30T00:00:00] )   
OR  
 ([Customer].[First Shipment Override].CURRENTMEMBER.MEMBERVALUE = [Customer].[First Shipment Override].&[2018-08-07T00:00:00] AND [Customer].[Legal Entities].CURRENTMEMBER.Member_Name = "Adilanti"  )   
THEN  
0  
ELSE  
Count  
(Filter  
(Existing([Customer].[Legal Entity]),  
[Time].[Month - Year].CurrentMember = [First Override Date].[Month - Year].CurrentMember.MEMBERVALUE  
)  
)  
END,  
ISIBLE = 1 , DISPLAY_FOLDER = 'KPI' , ASSOCIATED_MEASURE_GROUP = 'Invoices';  

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 3

Thank you guys for your help.
I have created a new measure group on top of the customer dimension. this measure group further connected with the Customer dimension and time dimension.