Forum Discussion
Count with OR Condition
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 )
)
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
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 )