Count with OR Condition

[DataMinion]

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 partners. To be considered active they need to satisfy two conditions,

Their service status is active

Their annual income is greater than £1

I have included three tables below, Sole Clients, Joint Clients and Income so I can either use the client from the sole clients table or the joint clients table.

I have created a measure that identifies if each client is active and I am stuck when I link these clients as I only want to count them both once, so in excel I would typically use the OR formula.

I've created a measure to identify if their income is greater than or equal to £1 which I then reference

ActiveFee = CALCULATE(COUNT(SoleClients[CRMContactId]), Income[Fee] >= 1)

Client 1 Active = CALCULATE(COUNT(Clients[CRMContactId]), ActiveStatus[Active]= "Active", FILTER(income, [ActiveFee]))

Client 2 Active = CALCULATE(COUNT(Clients[CRMContactID2]), ActiveStatus[Active]= "Active", FILTER(income, [ActiveFee]))

ActiveClients = IF(OR(CALCULATE(Count(Clients[CRMContactId]), ActiveStatus[Active]= "Active", FILTER(income, [ActiveFee])), CALCULATE(COUNT(Clients[CRMContactID2]), ActiveStatus[Active]= "Active", FILTER(income, [ActiveFee]))), 1, 0)

When I run this last measure it correctly shows 1 and 0 but it does not count the total as I would want to know for each adviser how many active clients they have. I imagine the result is text so using Value to convert so it sums the column doesn't work.

There's advantages to identify each sole client as being active so I would want to keep that, but I appreciate there may well be a way to combine client 1 and client 2 in the same measure.

Hopefully I've explained everying clearly and appreciate any help you can give me.

Thanks

[MarkLaf]

You may need to modify your model, then. Right now, Income only relates to SoleClients, so you can't get related income for Clients (at least leveraging your current physical relationships). Assuming there is a FK column in Income for Clients, you could achieve what you want with your current model using virtual relationships, but it would be better to redesign your model/relationships.

There are different ways to approach this depending on your requirements, but probably the simplest would be in Power Query to combine (append, not merge) all main clients from SoleClients and Clients and create a table of secondary clients, then define relationship MainClients--1:M-->Income and MainClients--1:M-->SecondaryClients. Note that a client could be in MainClients and SecondaryClients with this approach. Something like:

ActiveClients = 
CALCULATE(
    COUNTROWS( MainClients ),
    FILTER( MainClients, CALCULATE( SUM( Income[Fee] ) ) >= 1 ),
    FILTER(
        MainClients,
        "Active" IN UNION( { MainClients[ActiveStatus] }, VALUES( SecondaryClients[ActiveStatus] ) )
    )
)

If the above doesn't work for your particular data or requirements, it would probably be most helpful if you shared some dummy data for your current tables.

[DataMinion]

Hi,

Thanks @MarkLaf  for your help and patience. I'll try to explain further below as it could be either the way my model is setup or my explanation.

The tables are as follows

Sole Clients - this is all clients listed individually

Joint Clients - this is all clients listed jointly, Client 1 reference will be CRMContactID and client 2 reference is CRMContactID2. This reference corresponds to the same number the client has in the Sole Clients and Income tables and is what I use to create relationships

Income - this summarises all the clients income, listed individually

Sole Client

CRMContactId	FirstName
30599070	John
30599071	Bruce
30599072	Mildred
30599073	Sheila
30599074	Rupert

Joint Clients

CRMContactId	CRMContactID2	Client(s)
30599070		John
30599071	30599072	Bruce & Mildred
30599073		Sheila
30599074		Rupert

Income

ClientCRMContactId	Sum of Fee
30599070	£320.35
30599071	£0
30599072	£2,090.42
30599073	£746.44
30599074	£2,586.81

Model

I figured the relationship should be between the Sole Client table and the Income Table and then between the Sole Client table and the Joint Clients table. If I should be linking the Joint Clients table to the Income table then that could be part of the problem.

I do have other tables that I link and I use the Sole Clients table for that as well.

If there is a way to sum client 1 income and then sum client 2 income and finally add these together then that way I could use the joint income to determine whether the clients meet the criteria.

I'm happy to add columns if that is easier such as client 1 income, client 2 income and then add these together for joint income.

Hopefully this helps.

Thanks again

[MarkLaf]

So, with the above dummy data, should ActiveClients output to 4 or 5 (because, although Bruce has 0 fee, he still counts if you factor in joint client fee/status)?

[DataMinion]

Hi,

It still does not show them as Active, it is the 2nd client with the fee income and because client 1 fee income, in this case Bruce, is 0 then both the clients are not counted.

We need a way to count both clients income.

I've added columns now in the clients table by merging Clients and Income in Power Query, this now shows the income for each client and I used a measure to sum the income. The screenshot below shows their joint income to be £2,090 but as Bruce income is O the result of the measure is still blank where I would expect a 1

Ideally I dont want to merge tables as that seems slower than using measures but I just wanted to show that the end result is the same.

[MarkLaf]

Okay, I think I understand what you are going for. I was definitely confusing myself and seeing some dummy data helped. If I understand correctly now, you should be fine with your model as is, just add an inactive relationship between Clients and Income.

Then you can turn on the relationship to get the fee associated with Clients:

ActiveClients =
CALCULATE(
    COUNTROWS( SoleClients ),
    FILTER(
        SoleClients,
        VAR _clientsFee =
            CALCULATE(
                SUM( Income[Fee] ),
                USERELATIONSHIP ( Clients[CRMContactId2], Income[ClientCRMContactId] )
            )
        VAR _soleClientFee =
            CALCULATE( SUM( Income[Fee] ) )
        RETURN
            _soleClientFee + _clientsFee >= 1
    ),
    FILTER(
        SoleClients,
        "Active" IN { SoleClients[ActiveStatus], RELATED( Clients[ActiveStatus] ) }
    )
)

[DataMinion]

Hi,

Thanks @MarkLaf  I really appreciate your help with this.

I'm still not getting the desired results though and include a screenshot below of my model as it doesn't have the same relationship between the Clients and Income tables as yours.

Its a many to many relationship as not all clients on the Clients table have a partner so there are blanks for CRMContactID2 for some. I filtered these out but I still got the same message.

When I click on the Learn more link this takes me to documentation on the cardinality rules and suggests I can't use the RELATED function. I honestly do not know whether this will make an impact but just letting you know.

https://learn.microsoft.com/en-gb/power-bi/transform-model/desktop-many-to-many-relationships

I also don't understand this part of your function so thought I'd best check

FILTER(

        SoleClients,

        "Active" IN { SoleClients[ActiveStatus], RELATED( Clients[ActiveStatus] ) }

    )

There is no existing column SoleClients[ActiveStatus] I can add in but not sure what it means. I have another lookup table to determine what the Active Status is of the client, I can add a column if this will help.

Thanks again for your help

[MarkLaf]

Re: the Active IN filter, I had misread your formulas in your original post and missed that you have a separate ActiveStatus table. Similar to your issue with Income, you'll want to establish a relationship between Clients and ActiveStatus rather than solely rely on the SoleClient/ActiveStatus relationship or else you will just get data from other tables related to Clients[CRMContactId] and not Clients[CRMContactId2]. For now I'll just address the income conditional; we can tackle the active status if you can share details on relationship between ActiveStatus and SoleClient (i.e., is it on CRMContactId?).

I'm pretty sure we just need one tweak to get the previous measure working (along with removing active check for now at least): using CROSSFILTER to disable the SoleClients-->Income relationship.

ActiveClients_FeeOnly = 
CALCULATE(
    COUNTROWS( SoleClients ),
    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
    )
)

Alternatively, if it would be useful to pull out the embedded combined fee calc, you could split the above into two measures:

Combined Fee = 
VAR _clientsFee =
    CALCULATE(
        SUM( Income[Fee] ),
        USERELATIONSHIP ( Clients[CRMContactId2], Income[ClientCRMContactId] ),
        CROSSFILTER ( SoleClients[CRMContactId], Income[ClientCRMContactId], NONE ),
        DISTINCT( Clients[CRMContactId2] )
    )
VAR _soleClientFee =
    CALCULATE(
        SUM( Income[Fee] ),
        DISTINCT( SoleClients[CRMContactId] )
    )
RETURN
    _soleClientFee + _clientsFee


ActiveClients_FeeOnly_MeasureRef = 
CALCULATE(
    COUNTROWS( SoleClients ),
    FILTER( SoleClients, [Combined Fee] >= 1 )
)

[DataMinion]

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]

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
    )

[MarkLaf]

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]

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