locked
Create a set which is not effected by where clause in MDX. RRS feed

  • Question

  • By using below query, I want to get card IDs for DDDate in where clause but, Cash Loads date should NOT affect the set. But its not working. set is filtered by both dates in where clause. Please let me know if you have any solution. Thanks in advance.

    Query: 

    With SET [SetA]
     AS EXISTS([Card].[Card ID].[Card ID], ([DD Date].[Calendar Hierarchy].Currentmember , 
    [Cash Loads Date].[Calendar Hierarchy].[All])
    ,"Transactions")

    select NULL on 0,
    [setA] on 1

    from (select ([DD Date].[Calendar Hierarchy].[Date].&[20200621]) on 0
    from (select ([Cash Loads Date].[Calendar Hierarchy].[Date].&[20200624]) on 0
    from AmexServe
    )


    prajwal kumar potula

    Thursday, August 6, 2020 2:41 PM

All replies

  • What is the expected result?  

    In the from clause, you have first filtered the cube with the [Cash Loads Date].[Calendar Hierarchy].[Date].&[20200624], so it is the subcube has only this record.


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 7, 2020 6:39 AM
  • Thanks for reply . Expected result is, card id s with DD Date 20200621. That’s all. Set should not be filtered by cash load date. Ultimately, I am looking for count of cards.. please let me know if there is any other way of doing it . Thanks
    Sunday, August 9, 2020 1:15 AM
  • If Cash Loads date should NOT affect the set, then just delete "from (select ([Cash Loads Date].[Calendar Hierarchy].[Date].&[20200624]) on 0" this part from your query should be enough to have only dd date filter.

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 12, 2020 7:02 AM
  • Sorry, i think, I didn't provide you complete details. I need to create 2 sets. first set for DD cards which should be affected by DD Date only. and Set2 is Cash load cards which should only affected by Cash loads date. then, union of both sets. How do i do it?

    With SET [SetA]

     AS EXISTS([Card].[Card ID].[Card ID], ([DD Date].[Calendar Hierarchy].Currentmember , 
    [Cash Loads Date].[Calendar Hierarchy].[All])
    ,"Transactions")
    SET [SetB]
     AS EXISTS([Card].[Card ID].[Card ID], ([Cash Loads Date].[Calendar Hierarchy].Currentmember , 
    [[DD Date].[Calendar Hierarchy].[All])
    ,"Transactions")
    select NULL on 0,
    [setA] + [SetB] on 1

    from (select ([DD Date].[Calendar Hierarchy].[Date].&[20200621]) on 0
    from (select ([Cash Loads Date].[Calendar Hierarchy].[Date].&[20200624]) on 0
    from AmexServe
    )


    prajwal kumar potula

    Friday, August 14, 2020 6:26 PM
  • I've seen you open this in a new thread : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/560d5e79-9c94-4ccd-8224-5f0c55aeba7c/create-multiple-sets-and-union-them-in-mdx?forum=sqlanalysisservices

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 6:42 AM