Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Related Distinct Count

I'm trying to compute how many of our individuel product categories a customer has used, but I'm not getting the expected result. Attached image is a good of the data model in a classic setup with P...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

    Hi, you can use two measure like this:

     

    Main Groups Used =
CALCULATE (
    DISTINCTCOUNT ( 'Product'[ProductMainGroup] );
    CROSSFILTER ( 'Fact'[ProductID]; 'Product'[ProductId]; BOTH )
)

     

    Sub Groups Used =
CALCULATE (
    DISTINCTCOUNT ( 'Product'[Product Sub Group] );
    CROSSFILTER ( 'Fact'[ProductID]; 'Product'[ProductId]; BOTH )
)

     

    Regards 

     

    Victor

    Lima - Peru

     

nickchobotar's avatar
nickchobotar
Skilled Sharer
8 years ago

Anonymous

 

Here is another way to do this as a calculated column.

 

You might be better off with measures if you are running this over a large dataset. You can use the option suggested by Vvelarde  or simply create a bi-directional relationship between sales and products and use a simple DISTINCTCOUNT() measure.

 

MainGroup =
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( Proucts[ProductMainGroup] ),
        SUMMARIZE ( Sales, Sales[CustomerKey], Proucts[ProductMainGroup] )
    )
)


SubGroup =
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( Proucts[ProductSubGroup] ),
        SUMMARIZE ( Sales, Sales[CustomerKey], Proucts[ProductSubGroup] )
    )
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I'm aware that a measure might be better for performance considerations, however currently I might use the calculated columns for filtering purposes and more importantly, I want to perform correlations on the results, which I have asked about in this thread:

    Granularity in correlation plot where I'm uncertain how PBI handles granularity when feed directlt as the input for the correlation plot.

     

    Anyone care to share the logic behind their approaches, and what should perform best?
    I really want to understand the logic, and not just implement it, for future purposes.

     

    Unfortunatly, I'm away from the model atm. so can't test it.