Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello community members,
I have the following question
Next year, a few healthcare products will be categorized in a new way, therefore I made a calculated column with the following formula to define who is going to get which product.
ProductA =
IF(
ClientHash[Age] = "75 and older"
&& ClientHash[Product] = "01A04"
&& ClientHash[Volume] >= 3,
"Product A",
"Other product"
)
This works in the simple casus, but I have an issue in the following situation:
When I need to decide if someone gets in the future 'ProductB', they need to have the following requirements:
Client Age = 75 and older
AND
Client receives Product 01A04 or 02B04 with a Volume of 3 or more and Duration is >1
AND
Client recieves Product 07B03 or 07B04 or 07B05 with a Volume of 2 or more and Duration is >1
Can somebody help me with rewriting my initial formula? I get stuck with the product combination and the fact that the information of 1 client is specificied on more rows (each product per client = 1 row), see data example below
Client | Age | Volume | Duration | Product |
1 | 75 and older | 5 | 1,2 | 01A04 |
2 | 75 and older | 3 | 1,5 | 01A04 |
2 | 75 and older | 3 | 2 | 07B03 |
3 | 75 and older | 4 | 2,5 | 02B04 |
3 | 75 and older | 3 | 1,3 | 07B03 |
I hope to hear soon from you!
Best regards,
Sander
Solved! Go to Solution.
Hey @Anonymous ,
it's a little tricky to check that.
At the moment for me the most undertandable approach is to filter for the two complex product criteria on their own and if both criteria give a result per customer, then it should be Product B.
Check the following formula:
ProductB =
VAR vRowClient = ClientHash[Client]
VAR vCheckProduct1 =
FILTER(
ClientHash,
ClientHash[Client] = vRowClient
&& ClientHash[Age] = "75 and older"
&& ClientHash[Product] in { "01A04" ,"02B04"}
&& ClientHash[Volume] >= 3
&& ClientHash[Duration] > 1
)
VAR vCheckProduct2 =
FILTER(
ClientHash,
ClientHash[Client] = vRowClient
&& ClientHash[Age] = "75 and older"
&& ClientHash[Product] IN { "07B03", "07B04", "07B05" }
&& ClientHash[Volume] >= 2
&& ClientHash[Duration] > 1
)
RETURN
IF(
COUNTROWS( vCheckProduct1 ) > 0 && COUNTROWS( vCheckProduct2 ) > 0,
"Product B",
"Other product"
)
Hey @Anonymous ,
it's a little tricky to check that.
At the moment for me the most undertandable approach is to filter for the two complex product criteria on their own and if both criteria give a result per customer, then it should be Product B.
Check the following formula:
ProductB =
VAR vRowClient = ClientHash[Client]
VAR vCheckProduct1 =
FILTER(
ClientHash,
ClientHash[Client] = vRowClient
&& ClientHash[Age] = "75 and older"
&& ClientHash[Product] in { "01A04" ,"02B04"}
&& ClientHash[Volume] >= 3
&& ClientHash[Duration] > 1
)
VAR vCheckProduct2 =
FILTER(
ClientHash,
ClientHash[Client] = vRowClient
&& ClientHash[Age] = "75 and older"
&& ClientHash[Product] IN { "07B03", "07B04", "07B05" }
&& ClientHash[Volume] >= 2
&& ClientHash[Duration] > 1
)
RETURN
IF(
COUNTROWS( vCheckProduct1 ) > 0 && COUNTROWS( vCheckProduct2 ) > 0,
"Product B",
"Other product"
)
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |