Forum Discussion
Count with OR Condition
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.
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
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)?
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.
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] ) } ) )