Forum Discussion

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

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

CRMContactIdFirstName
30599070John
30599071Bruce
30599072Mildred
30599073Sheila
30599074Rupert

 

Joint Clients

CRMContactIdCRMContactID2Client(s)
30599070 John
3059907130599072Bruce & Mildred
30599073 Sheila
30599074 Rupert

 

Income

ClientCRMContactIdSum 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's avatar
MarkLaf
Icon for Super User rankSuper User
3 years ago

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

    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's avatar
      MarkLaf
      Icon for Super User rankSuper User
      3 years ago

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

        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