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...
DataMinion's avatar
DataMinion
Helper I
3 years ago

Hi MarkLaf thanks again for your help with this.

 

Whether its the right approach or not on my part but I thought it would be useful to create a Dimension table for the ActiveStatus. We use a service status to describe the relationship we have with our clients and this may expand in time. I need to identify the 'Active' clients.

 

 

The relationship is between ActiveStatus[ServiceStatusName] and SoleClients[ServiceStatusName]

There is also a column in the Clients table for each client with the same information if it is easier to link the ActiveStatus table directly to the Clients table.

We call a client 'Active' when their Service Status is Active and their income is >= £1.

 

I hope that helps. I would be more than happy to share more information and dummy data if that helps.

 

The ActiveClients_Fee only measure does now count all clients where there is income >= £1 so now we just need to factor in their active status.

 

Thanks again

MarkLaf's avatar
MarkLaf
Super User
3 years ago

I think this will work

 

ActiveClients =
VAR _SoleClientDirectActive =
    FILTER( SoleClients, RELATED( ActiveStatus[Active] ) = "Active" )
VAR _SoleClientIndirectActive =
    FILTER(
        CALCULATETABLE(
            SoleClients,
            USERELATIONSHIP ( ActiveStatus[ServiceStatusName], Clients[ServiceStatusName] )
        ),
        RELATED( ActiveStatus[Active] ) = "Active"
    )
VAR _SoleClientAllAcive =
    DISTINCT( UNION( _SoleClientDirectActive, _SoleClientIndirectActive ) )
VAR _AllClientsValidFee =
    FILTER(
        SoleClients,
        VAR _clientsFee =
            CALCULATE(
                SUM( Income[Fee] ),
                USERELATIONSHIP ( Clients[CRMContactId2], Income[ClientCRMContactId] ),
                CROSSFILTER ( SoleClients[CRMContactId], Income[ClientCRMContactId], NONE )
            )
        VAR _soleClientFee =
            CALCULATE( SUM( Income[Fee] ) )
        RETURN
            _soleClientFee + _clientsFee >= 1
    )
RETURN
    CALCULATE(
        COUNTROWS( SoleClients ),
        _SoleClientAllAcive,
        _AllClientsValidFee
    )