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 )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi Anonymous

 

And this column one for Sub Groups used

 

Sub Groups Used =
VAR mytable =
    ADDCOLUMNS (
        RELATEDTABLE ( Sales ),
        "MainGroup", CALCULATE ( VALUES ( 'Product'[Product Sub Group] ) )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Product'[Product Sub Group] ), mytable )