none
MDX count distinct calculation where another column

    Question

  • Hi All,

    i have a fact table like the following

     

    1-episodeKey (not unique)

    2-Readmission (takes value null or 'readmission' may i chaneg to (0 or 1) if that help solve the problem)

    3-date

    4-other dimesnions foreign keys

     

    i want to count distinct episode keys where readmission is not null,

    so how can i write a calculation that make that count ?

     

     

     


    Ahmed Salah
    Saturday, September 25, 2010 10:33 AM

All replies

  • Hi Ahmed,

    I assume episode and readmission currently are dimensions. Now, if your epsiodeKey is an integer field (is it ?), you can also use it as a measure in the cube. Set it's aggregation function to 'Distinct Count'.

    Now you can simply use this newly created measure in your query  and by filtering on the readmission dimension with value 'readmission' you get your required result.

    hth,

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Saturday, September 25, 2010 12:34 PM
  • Hi Cees,

    thanks for your reply.

    the episode and readmission are  not dimensions.

     

    i supposed the following solution but i'm still testing it. i add a new field consisting of episodekey +'-'+readmission  and added that field as a measure with no agregation

     

    now i will try to make a calcualation to make distinctcount. on the new filed (new measure) is that true ?


    Ahmed Salah
    Saturday, September 25, 2010 2:10 PM
  • Ahmad,

    Utilizing Distinct count aggregation function is one approach but usually that impacts the performance.  Instead of this you may want to create DSV level query to calculate this by using a CASE statement

     

     


    Thanks, Ashok My Blog http://www.dugaputiashok.blogspot.com
    Saturday, September 25, 2010 2:11 PM
  • You can also use the DISTINCTCOUNT function in the MDX and then use FILTER function or NON EMOTY function

    DISTINCTCOUNT sample

    WITH SET MySet AS

    {[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Australia],

    [Customer].[Customer Geography].[Country].&[Canada],[Customer].[Customer Geography].[Country].&[France],

    [Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[Country].&[United Kingdom]}

    *

    {([Date].[Calendar].[Date].&[20010701],[Measures].[Internet Sales Amount] )}

    //Returns the value 3 because Internet Sales Amount is null

    //for the UK on the date specified

    MEMBER MEASURES.SETDISTINCTCOUNT AS

    DISTINCTCOUNT(MySet)

    SELECT {MEASURES.SETDISTINCTCOUNT} ON 0

    FROM [Adventure Works]


    vinu
    Saturday, September 25, 2010 3:51 PM
  • Hi Ahmed,

    If you can create a named calculation with something like this pseudo code iif(Readmission is null, null, episodeKey), and then you can simply create a distinctcount aggregation on this new filed, is that work in your scenario?

     

    Hope this helps,

    Raymond
    Raymond Li - MSFT
    Sunday, October 03, 2010 4:16 AM
    Moderator