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

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

First, I didn't know you can pass tables as variables ? 

Second, I think I understand what you're trying to do, but after having entered your suggestion but model is just "stuck" on working on it, so I don't think the solution is ideal.

 

The dataset I'm working on has +120.000 Customers, around 1m sales rows distributed over 2000 products, if that has any relevance to performance.

Vvelarde's avatar
Vvelarde
Community Champion
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