DAX Formula to count if value is contained in another column

[Lad25]

Hi everyone.

I've been searching for ways to do this, but haven't found anything yet.

I have 2 tables, one has a list of Products that we sell (1 column, unique values), while the other table has the results of a survey made to a sample of clients, which have all of the products that each client is selling in a field, sepparated by comma.

Here is an example of what I have

Products Table

Products

Product A

Product B

Product C

Product D

Survey table

Client	Registered products for sale
Client A	Product A, Product B, Product D
Client B	Product B, Product C
Client C	Product A, Product D
Client D	Product B
Client E	Product A, Product B, Product C, Product D

What I'm trying to achieve is something similar to this

Product	Count
Product A	3
Product B	4
Product C	2
Product D	3

I managed to obtain a table with a calculated column that outputs the Count, but the issue is that I want to filter by the client name, to see which products he doesn't sell, and the filter doesn't change the calculations. I tried relating the tables, but since the values are comma separated, the calculations weren't correct.

The DAX Formula for the calculated column I'm using is the following (TDatos is the survey table in the example above, and TSKU is the product table):

Column = CALCULATE (
    COUNTROWS ( TDatos ),
    FILTER (
        ALLSELECTED ( TDatos ),
        CONTAINSSTRING ( TDatos[SKU vendidos], TSKU[SKU] )
    )
)

Any hints are appreciated, thanks!

[sevenhills · Accepted solution]

Assuming you tables are "Products", "Clients", and they are not related:

Add measure to Products table

M1 = 

var _c = SELECTEDVALUE(Products[Products])

RETURN COUNTROWS( filter( ALLSELECTED(Clients), CONTAINSSTRING( Clients[Registered products for sale], _c)))

Sample output 

*no selection of client*

*select clients*

Hope it helps!

[Lad25]

Thanks! This is exactly what I needed, much appreciated

[sevenhills]

Glad it helped!

Optional: appreciate if you can give kudos 🙂