Forum Discussion

DataMinion's avatar
DataMinion
Helper I
3 years ago

Count with OR Condition

Hi,   I'm new to using Power BI so looking for help please on a problem I can't resolve.   I'm trying to count whether client 1 or client 2 is active, these clients are related and usually partne...
MarkLaf's avatar
MarkLaf
Super User
3 years ago

This may be what you want.

ActiveClients = 
CALCULATE(
    COUNTROWS( SoleClients ),
    FILTER( SoleClients, CALCULATE( SUM( Income[Fee] ) ) >= 1 ),
    FILTER(
        SoleClients,
        "Active" IN { SoleClients[ActiveStatus], RELATED( Clients[ActiveStatus] ) }
    )
)

 

 

DataMinion's avatar
DataMinion
Helper I
3 years ago

Hi MarkLaf ,

 

Thank you for this, your solution is much improved on my try, however there is one issue that means not all clients are pulled through and you wouldn't have known this from my original post so I'm wondering if you can include this as well please.

For the clients, in most cases client 1 is the main client but in some cases client 2 is the main client. Your solution is only counted when client 1 is the main client.

Client 1 might not have fees of more than £1 but client 2 does so there are clients that have not been counted. Screenshot below shows that the joint fee income for these clients is zero yet client 2 generates over £2k

So as well as linking the clients together I need to link their income together as well, is this possible at at all.

So the result should be

If client 1 is active and income > £1 or If client 2 is active and income > £1 then count else don't.

 

I hope that makes sense and thanks again for your help