cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 Product-, Sales- and Customer Table.

The green columns are the ones I'm trying to compute, but current DAX formula is only returning a distinct count of all of the Product table, and not the table filtered by actual sales.

 

As far as I understand from Dax Patterns , all I need to do for each column is to use the following code:

Main Groups Used =
CALCULATE( 
  DISTINCTCOUNT(Product[ProductMainGroup]),
  Sales)

Sub Groups Used = 
CALCULATE(
   DISTINCTCOUNT(Product[Product Sub Group]),
   Sales)

Where CALCULATE should ensure that current CustomerKey row context is applied, and then filter the sales table accordingly, to only return a distinct count of the items the customer has bought.

My search lead me to a solution that suggested to apply "both" for crossfiltering between the tables, but my data model will not allow me do to so, complaining about only allowing one filtering path between tables.

 

So, how do I achieve my desired result without looking for a reconfiguration of my data model?

 

Related Distinct.PNG

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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

 




Lima - Peru

View solution in original post

7 REPLIES 7
nickchobotar
Continued Contributor
Continued Contributor

@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
Not applicable

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.

Zubair_Muhammad
Community Champion
Community Champion

@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 )
Regards
Zubair

Please try my custom visuals


Anonymous
Not applicable

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
Community Champion
Community Champion

@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

 




Lima - Peru

View solution in original post

Hi

Try simplified version

Calculate (distinctcount(product[Maingroup]), relatedtable(sales))
Regards
Zubair

Please try my custom visuals


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 )
Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors