none
how add below query in SSAS Multidimensional Cube Calculations..

    Question

  • Hi ,
    we are trying below query in MDX,this is working fine.
    how to add this query in SSAS Multidimensional Cube Calculations.


    ( SELECT ( FILTER
      (
      [Dim Date].[Date].[Date] * [Dim Location].[Startdate].[Startdate],
    [Dim Date].[Date].CurrentMember.Name >= [Dim Location].[Startdate].CurrentMember.Name
      ) ) 

    Thanks in Advance.

    ch.kishore

    Friday, July 6, 2018 9:50 AM

All replies

  • What you have here is not a complete MDX query, but rather its part, a subquery. You can add this to cube calculations as a calculated set:
    CREATE SET CURRENTCUBE.[use your own name here] AS
    FILTER
      (
      [Dim Date].[Date].[Date] * [Dim Location].[Startdate].[Startdate],
      [Dim Date].[Date].CurrentMember.Name >= [Dim Location].[Startdate].CurrentMember.Name
      );

    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, July 6, 2018 10:00 AM
  • Hi Alexei Stoyanovsky,
    Thanks for your Reply,we need to add measure name  in filter condition .

    Thanks,
    Kishore.CH


    ch.kishore

    Friday, July 6, 2018 12:25 PM
  • Why, do feel free to add any conditions you need. That is your cube anyway :)

    If you mean that you want this as a calculated measure, well, the Filter function returns a set, which is something a measure is not allowed to. You'll have to add some MDX that will ultimately return a single value.


    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, July 6, 2018 1:11 PM
  • I need to add below condition in Multidimensional calculated member,please correct below condition
    ,
    ( FILTER

      (
      [Dim Date].[Date].[Date] * [Dim Location].[Startdate].[Startdate],
    [Dim Date].[Date].CurrentMember.Name >= [Dim Location].[Startdate].CurrentMember.Name
      ) ,Measures.Actual) 

    ch.kishore

    Friday, July 6, 2018 1:44 PM
  • Hi Kishore,

    You could create a dynamic set and for the above condition and any measure you drop against this named set will always filter accordingly. Something like below which I have done for Adventure works sample database.

    WITH DYNAMIC SET [Test] AS
    (FILTER
      ([Order Date].[Calendar Year].[Calendar Year] *[Ship Date].[Calendar Year].[Calendar Year],
    [Order Date].[Calendar Year].CurrentMember.Name <
    [Ship Date].[Calendar Year].CurrentMember.Name
     ) )
    
    
    
    SELECT NON EMPTY [Test]
    
    On ROWS, {[Measures].[Order Quantity],[Sales Amount]} ON COLUMNS FROM [Training]

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps


    Friday, July 6, 2018 2:16 PM
  • Hi kishore.ch,

    Thanks for your question.

    >>>I need to add below condition in Multidimensional calculated member, please correct below condition
    According to your description, you might want to try below MDX code:

    Create Member CurrentCube.[Measures].[Your Measures Name] AS
    Aggregate( 
       FILTER
      (
      [Dim Date].[Date].[Date] * [Dim Location].[Startdate].[Startdate],
      [Dim Date].[Date].CurrentMember.Member_key >= 
      [Dim Location].[Startdate].CurrentMember.Member_key
      ) ,Measures.[Actual])


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Monday, July 9, 2018 10:42 PM
    Monday, July 9, 2018 2:41 AM
    Moderator

  • ch.kishore

    Wednesday, July 11, 2018 8:27 AM
  • Hi kishore.ch,

    Thanks for your response.

    In this scenario, you might need to verify below query in your cube, see if it will return any data for [Measures].[Actual]:

    Select [Measures].[Actual] on 0,
    FILTER
      (
      [Dim Date].[Date].[Date] * [Dim Location].[Startdate].[Startdate],
      [Dim Date].[Date].CurrentMember.Member_key >= 
      [Dim Location].[Startdate].CurrentMember.Member_key
      )  on 1
    From [Your Cube]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 11, 2018 8:38 AM
    Moderator
  • Hi Willson Yuan,
    Thanks for Your Reply,

    Above scenairo ,how to add in Multidimensional cube calculations Expression .

    ch.kishore

    Wednesday, July 11, 2018 9:39 AM
  • Hi kishore.ch,

    Thanks for your response.

    >>>Above scenairo ,how to add in Multidimensional cube calculations Expression .

    I have already shared it to you. Never mind, I will posted it again:

    Create Member CurrentCube.[Measures].[Your Measures Name] AS
    Aggregate( 
       FILTER
      (
      [Dim Date].[Date].[Date] * [Dim Location].[Startdate].[Startdate],
      [Dim Date].[Date].CurrentMember.Member_key >= 
      [Dim Location].[Startdate].CurrentMember.Member_key
      ) ,Measures.[Actual])

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 11, 2018 9:43 AM
    Moderator
  • Hi Willson Yuan,
    we are Trying to cube,that is not working,I am sharing Screen Shot also,
    If any posibilities, please share your adding Screen Shots.

    ch.kishore

    Wednesday, July 11, 2018 9:51 AM
  • Hi kishore.ch,
     
    Thanks for your response.

    >>>we are Trying to cube,that is not working,I am sharing Screen Shot also,If any posibilities, please share your adding Screen Shots
    Please refer to below MDX query that works for me, I have tested in cube AdventureWorks:

    select [Measures].[Internet Sales Amount] on 0, 
    FILTER
      (Nonempty([Delivery Date].[Date].[Date]*
                [Date].[Date].[Date],
    			[Measures].[Internet Sales Amount]),
      [Delivery Date].[Date].CurrentMember.Member_key >=
      [Date].[Date].CurrentMember.Member_key) on 1
    From [Adventure Works]
    Where [Date].[Calendar Year].&[2012]

    In your scenario, you can add nonempty function to eliminate the null value:

    Create Member CurrentCube.[Measures].[Your Measures Name] AS
    Aggregate( 
       FILTER
      (
      Nonempty([Dim Date].[Date].[Date] *
         [Dim Location].[Startdate].[Startdate],
         [Measures].[Actual]),
      [Dim Date].[Date].CurrentMember.Member_key >= 
      [Dim Location].[Startdate].CurrentMember.Member_key
      ) ,[Measures].[Actual]);


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Saturday, August 18, 2018 12:32 PM
    Thursday, July 12, 2018 7:15 AM
    Moderator
  • Hi Willson Yuan,
    I need to Display only DimDate and Internet Sales Amount,
    using Calculate Columns,Is there any posibilites to show DImdate and InternetSalesAmount in Fiscal Year..

    Thanks & Regards,
    Kishore CH

    ch.kishore

    Saturday, August 18, 2018 5:24 AM