Post reply

Testing a dimension value for Unknown

  • June 7, 2011 at 12:34 pm

    #239578

    Ok I have a calculation defined as

    [Measures].[Measures Exceptions Count]/IIF(STRTOVALUE([Agencies].[Agency List].Properties( "User Count" ))<1,1,IIF(ISEMPTY([Agencies].[Agency List].Properties( "User Count" )),1,[Agencies].[Agency List].Properties( "User Count" )))

    And this works for all of my Agencies. but I also have an unknown value on the Agencies dimension set for Agencies that do not match my Agencies dimension.

    How can I test for the Unknown value in a calculation? I can't seem to find a function for that.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • June 7, 2011 at 2:34 pm

    #1335327

    Finally came up with a fix...

    wrapped the whole thing in

    IIF(ISERROR(originalcalculation),measurevalue,originalcalculation)

    It works... not as nicely as I believe it should... but it works.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply