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

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Anonymous

 

Try this calculated column in Customer Table

 

Main Groups Used =
VAR mytable =
    ADDCOLUMNS (
        RELATEDTABLE ( Sales ),
        "MainGroup", CALCULATE ( VALUES ( 'Product'[ProductMainGroup] ) )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Product'[ProductMainGroup] ), mytable )
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.

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago
    Hi

    Try simplified version

    Calculate (distinctcount(product[Maingroup]), relatedtable(sales))
  • 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