Flag for Products common with Customers

[sagar_r01]

I want to set Flag for Products based on its common usage by the customers.

Below is the schema of my table. We have 5 customers with what products (3 products for example) they are using in a given month. So my requirement is to set a flag for each product which would set to YES / NO based on the Product used by Customer

For Example, Customers 1, 2, and 3 all use Product A, so Yes should come in for all the rows under the column "Does Customer Have Product A" for Customer 1, 2, and 3. Similarly for Does Customer Have Product B and Does Customer Have Product C

And if the Customer has all 3 Products, all the rows for that customer in the column Does Customer Have Product A, B and C should be Yes

The DAX should also check the month also while setting the flag.

Schema and Expected Columns

MonthEnd	Customer	Product	Column 1	Column 2	Column 3	Does Customer Have Product A	Does Customer Have Product B	Does Customer Have Product C	Does Customer Have Product A, B and C
1/31/2020	1	A				Yes	Yes	Yes	Yes
1/31/2020	1	B				Yes	Yes	Yes	Yes
1/31/2020	1	C				Yes	Yes	Yes	Yes
1/31/2020	2	A				Yes	Yes	No	No
1/31/2020	2	B				Yes	Yes	No	No
1/31/2020	3	A				Yes	No	No	No
1/31/2020	4	B				No	Yes	No	No
1/31/2020	5	C				No	No	Yes	No

Thanks

Sagar

[v-juanli-msft · Accepted solution]

Hi @sagar_r01 

Create calculated columns

allproducts = CONCATENATEX(FILTER('Table','Table'[MonthEnd]=EARLIER('Table'[MonthEnd])&&'Table'[Customer]=EARLIER('Table'[Customer])),[Product],",")

Does Customer Have Product A = IF(FIND("A",[allproducts],1,0),"Yes","No")

Does Customer Have Product B = IF(FIND("B",[allproducts],1,0),"Yes","No")

Does Customer Have Product C = IF(FIND("C",[allproducts],1,0),"Yes","No")

Does Customer Have Product A,B,C = IF(FIND("A",[allproducts],1,0)&&FIND("B",[allproducts],1,0)&&FIND("C",[allproducts],1,0),"Yes","No")

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @sagar_r01 ,

I'm not sure if I understand your requirement 100%.

I created a measure:

has product = 
var pathProduct = CONCATENATEX(ALL('Table'[Product]) , [Product] , "|" , [Product] , ASC)
var pathProductLength = PATHLENGTH(pathProduct)
return
IF(HASONEVALUE('Table'[Customer])
    ,IF(AND(HASONEVALUE('Table'[Product]) , ISFILTERED('Table'[Product]))
        , IF(
            PATHCONTAINS(pathProduct , MAX('Table'[Product]))
            , "Yes"
            , BLANK()
        )
        , 
        var noOfProducts =
            SUMX(
                VALUES('Table'[Product])
                , IF(
                    PATHCONTAINS(pathProduct , [Product])
                    , 1
                    , BLANK() --"No"
                )
            )
        return
        IF(noOfProducts = pathProductLength
            , "Has All Products" --"Yes"
            , CONCATENATE("No of Products: " , noOfProducts) --"No"
        )
    )
    ,BLANK()
)

This measure allows to creates this matrix visual:

Within the measure, a string with all products is created by using the iterator function CONCATENATEX, by using the "|" as a separator it's possible to use the PATH... functions.

Of course, you can use "No" where I use BLANK or on the row total "Yes" and "No".

If you need calculated columns this will become more complex as a dedicated table has to be created with a dynamic number of columns as the number of products may vary.

Nevertheless, I hope this provides some ideas on how to tackle your challenge.

Regards,

Tom

[sagar_r01]

Thanks for the reply, the reason I was looking for the flags as a calculated column as I need these flags further to perform some more calculations.