MDX script needed

ETL vs ELTL 26 Reputation points
2020-12-10T08:59:49.747+00:00

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

46891-capture.jpg

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';

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,411 Reputation points
    2020-12-11T08:00:17.803+00:00

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

    1 person found this answer helpful.
    0 comments No comments

  2. Lukas Yu -MSFT 5,816 Reputation points
    2020-12-11T07:36:52.197+00:00

    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


  3. ETL vs ELTL 26 Reputation points
    2020-12-14T14:58:12.36+00:00

    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.