locked
How to use EXCEPT in a slicer? RRS feed

  • Question

  • Hi all,

    I tried to use the Except function like this:

    SELECT NON EMPTY ORDER({[Bi Employee].[Employee Key].CHILDREN}, [Measures].[Bi Measure Count], DESC) ON AXIS(0),
           [Measures].[Bi Measure Count] ON AXIS(1)
    FROM  [mycube]
    WHERE  ([Bi Rank In Trip].[Rank Key].&[1],
            [Bi Date].[Year].&[2015],
            EXCEPT({[Bi Route].[Route Key].CHILDREN}, {[Bi Route].[Route Key].&[ 1005]}))

    Because I want all Route_key except ' 1005'. But based on the results (which are not good), its clear to me that this function do not act the way I think.

    How can I exclude Route Key ' 1005' otherwise?

    Best regards.

    Carl


    Carl Bruneau

    Wednesday, November 11, 2015 8:55 PM

Answers

  • Except is the function you would use to exclude a member. Can you define what you mean by the results being "not good". As Amir has suggested temporarily putting the set on the rows may help see what is coming back and if that is what you expected.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by CBatWork Thursday, November 12, 2015 1:35 PM
    Thursday, November 12, 2015 2:27 AM

All replies

  • Hi,

    Instead of using Except in where clause try using it on Axis(1)  as below

    SELECT NON EMPTY ORDER({[Bi Employee].[Employee Key].CHILDREN}, [Measures].[Bi Measure Count], DESC) ON AXIS(0),
           EXCEPT({[Bi Route].[Route Key].CHILDREN}, {[Bi Route].[Route Key].&[ 1005]}) ON AXIS(1)
    FROM  [mycube]
    WHERE  ([Bi Rank In Trip].[Rank Key].&[1],
            [Bi Date].[Year].&[2015]
            )


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page

    Thursday, November 12, 2015 1:32 AM
  • Except is the function you would use to exclude a member. Can you define what you mean by the results being "not good". As Amir has suggested temporarily putting the set on the rows may help see what is coming back and if that is what you expected.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by CBatWork Thursday, November 12, 2015 1:35 PM
    Thursday, November 12, 2015 2:27 AM
  • Hi CBatWork,

    In MDX, using EXCEPT() is the proper way to remove a member from a set, there's no syntax issue in your MDX. You can direct use EXCEPT() on axis to see if it can return your expect set as Tomar suggested. I think the reason why you get incorrect result is the improper relationship existing in your cube design. When we put member and set in where clause, SSAS will crossjoin all sets and slice the measure appear on axis. If those involved dimension tables has improper relationship with the fact table, it may return same result on each row. So please check the Dimension Usage in your cube.

    Regards,


    Simon Hou
    TechNet Community Support


    Thursday, November 12, 2015 9:12 AM
  • With all your suggestions I was able to confirm that the results were finally good. I made a mistake verifying the results yesterday.

    Thanks to all of you guys.

    Have a nice day.

    Carl


    Carl Bruneau

    Thursday, November 12, 2015 1:39 PM