cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clo_64
Frequent Visitor

How to create a filter with count

I would like to create a short table showing how much customers have bought how much product,s and I'm struggling getting the COUNT or COUNTROWS working as an expression within CALCULATE/CALCULATETABLE.

Here my table :

clo_64_0-1655149478965.png

And would like to get:
Nbr of Products     Nbr of Customers
1                             3
2                             2

How do I do that with DAX?
Thanks

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @clo_64,

 

I think I got to a solution:

tomfox_0-1655152321661.png

 

 

Here the DAX:

New Table = 
VAR _helpTable =
SUMMARIZE (
    TableCustomerProduct,
    TableCustomerProduct[Customer],
    "Nbr of Products", DISTINCTCOUNT ( TableCustomerProduct[Product] )
)
RETURN
SUMMARIZE (
    _helpTable,
    [Nbr of Products],
    "Nbr of Customers",
    CALCULATE (
        DISTINCTCOUNT ( 'TableCustomerProduct'[Customer] ),
        FILTER (
            VALUES ( 'TableCustomerProduct'[Customer] ),
            CALCULATE ( DISTINCTCOUNT ( TableCustomerProduct[Product] ) ) = [Nbr of Products]
        )
    )
)

 

The _helpTable does a grouping on customer with a distinct count on product. The return statement uses the Number of Products from the _helptable combined  with a "having clause" in order to retrieve the number of customers per number of products

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
tackytechtom
Super User
Super User

Hi @clo_64,

 

I think I got to a solution:

tomfox_0-1655152321661.png

 

 

Here the DAX:

New Table = 
VAR _helpTable =
SUMMARIZE (
    TableCustomerProduct,
    TableCustomerProduct[Customer],
    "Nbr of Products", DISTINCTCOUNT ( TableCustomerProduct[Product] )
)
RETURN
SUMMARIZE (
    _helpTable,
    [Nbr of Products],
    "Nbr of Customers",
    CALCULATE (
        DISTINCTCOUNT ( 'TableCustomerProduct'[Customer] ),
        FILTER (
            VALUES ( 'TableCustomerProduct'[Customer] ),
            CALCULATE ( DISTINCTCOUNT ( TableCustomerProduct[Product] ) ) = [Nbr of Products]
        )
    )
)

 

The _helpTable does a grouping on customer with a distinct count on product. The return statement uses the Number of Products from the _helptable combined  with a "having clause" in order to retrieve the number of customers per number of products

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Excellent! Very smart this double SUMMARIZE!
Thanks Tom

ManguilibeKAO
Resolver I
Resolver I

Hi,

 

Coulld you explain why you have 2 2 on the second line of your output?  Shouldn't it be 2 1, as only the customer Gamma has bought 2 products?

 

Best regards.

Because Ypsilon has also bought 2 distint products.

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors