Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |