none
Circular Reference error in the Excel sheet for Measure but works in SSAS Browser

    Question

  • Hello,

    I'm facing a problem with one of my measure when dropped on the excel(2010). It gives me the error of circular refference i.e.

    "The definition of the MachineCountSet set contains a circular reference". Here is how my machinecount set looks like

    Filter
    (

    NONEMPTY({[Machines].[Machine Serial].[Machine Serial].Members *
             [Machines].[Create].CurrentMember *
       [Machines].[Disconnect].CurrentMember},[Measures].[Cash Played])
    ,
    (
    [Machines].[Create].CurrentMember.Member_Value  <= [BusinessDay].[Business Date].CurrentMember.Member_Value    AND
    [Machines].[Disconnect].CurrentMember.Member_Value >= [BusinessDay].[Business Date].CurrentMember.Member_Value
    )

    OR [Machines].[Deleted].&[False]

    )

    Surprisingly, it works in SSAS Browser. Any idea why its giving a circular reference error in excel?

    Thanks

    Friday, October 18, 2013 3:34 PM

All replies

  • You're statement doesn't really make any sense. What exactly are you trying to do? You pass a CROSSJOIN as the first parameter of the Filter function that is composed of all members of the [Machines].[Machine Serial].[Machine Serial] level but only one member from the [Machines].[Create] and [Machines].[Disconnect] hierarchies. Then, the second argument of your Filter function only references properties of the [Machine].[Create] and [Machine].[Disconnect] hierarchies and nothing of the [Machine Serial] level. Whatever you're trying to do, is probably only working in a very specific test case that you're executing with the SSAS Browser.

    &lt;a href=&quot;http://martinsbiblog.spaces.live.com&quot; target=&quot;_blank&quot;&gt;http://martinmason.wordpress.com&lt;/a&gt;

    Saturday, October 19, 2013 12:45 PM
  • Thanks Martin for your reply.

    Let me put some more information regarding my requirement. 

    Requirement:

    To get Distinct machine count on given business day which is Less than equal to Machine Create Date and greater than equal to Machine Disconnect Date.

    For the above i had initially created a Dynamic named set (MachineCountSet) which filters the machines less than Create date and greater than Machine Disconnect Date.

    The Named set looked like

    filter
    (

    NONEMPTY({[Machines].[Machine Serial].[Machine Serial].Members },[Measures].[Cash Played])
    ,
    (
    [Machines].[Create].CurrentMember.Member_Value  <= [BusinessDay].[Business Date].CurrentMember.Member_Value    AND
    [Machines].[Disconnect].CurrentMember.Member_Value >= [BusinessDay].[Business Date].CurrentMember.Member_Value
    )

    OR [Machines].[Deleted].&[False]

    )

    Then i created a calculated measure called Units

    IIF( 
    DISTINCTCOUNT(EXISTS([Machines].[Machine Serial].[Machine Serial].Members ,MachineCountSet)) = 0, NULL,
    DISTINCTCOUNT(EXISTS([Machines].[Machine Serial].[Machine Serial].Members ,MachineCountSet))
    )

    Now when i drag and drop this measure on Excel 2010. It gives me a circular reference error. But works perfectly fine on the SSAS Browser giving me proper units.

    Let me know if this clarifies what i am facing as issue.

    Thanks

    Monday, October 21, 2013 8:43 PM