Help with if function

[Anonymous]

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

[selimovd · Accepted 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"
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd Thanks for your help! This works perfect!