0

DistinctCount with filter

I have a fact table

  • Impressions (ImpressionID, DeviceID, AudienceID)

and two dimension tables

  • Devices (DeviceID, DeviceType, etc)
  • Audiences (AudienceID, AudienceType, etc)

I have a Formulate called All Audiences Except Techs which is a list of all audiences except Techs

{Except(AllMembers([Audiences].[Audience]),[Audiences].[Audience].[Techs])}

 

 

I have another Formulate to calculate the total number of impressions for all audiences except Techs which works as expected:

Count({[Audiences].[Audience].*[272cf8c2-2350-4c51-bcd0-730258cbef37]},([measures].[Impressions Impressions]))

 

 

I would like to get a distinct count of DeviceIDs for impressions for all audiences except Techs, however the formula below throws the error "The DistinctCount function expects one of the following expressions MEASURE or MODEL ATTRIBUTE as an argument. A SET OF MEMBERS expression was used."

DistinctCount({[Audiences].[Audience].*[272cf8c2-2350-4c51-bcd0-730258cbef37]},([measures].[Impressions Devices]))

 

 

 How to I get a distinct count of devices for impressions for all audiences except Techs ?  

Thanks.

1 reply

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 3 days ago
    • Reported - view

    Hi 

    The approach is to create the measures in the model, and the aggregations in the formulate.

    I assume [measures].[Impressions Impressions] is defined as a count() in the model, and [measures].[Impressions Devices] is defined as a distinct count on the deviceID in the model.
    All you need to do now, is Aggregate your list {All Audiences Except Techs} with the measure you want.

    So this will be the count

    Aggregate({[Audiences].[Audience].*[272cf8c2-2350-4c51-bcd0-730258cbef37]},([measures].[Impressions Impressions]))

    and this will be the distinct count:

    Aggregate({[Audiences].[Audience].*[272cf8c2-2350-4c51-bcd0-730258cbef37]},([measures].[Impressions Devices]))

    The aggregate function will maintain the original aggregation of the measure (count, distinct count) and use it on the list   {All Audiences Except Techs}.

     

    Another option is to simply create a formulate:

    Aggregate({[Audiences].[Audience].*[272cf8c2-2350-4c51-bcd0-730258cbef37]})

    In the context of the [Audiences] table and the [Audience] column, name it "all except tech" (this may justify renaming the list), and then when you show it on the discover - it will aggregate whichever measure you use.

     

    Regards,

    Moshe

Login to reply

Content aside

  • Status Answered
  • 3 days agoLast active
  • 1Replies
  • 15Views
  • 2 Following