[Be Careful! Newbie!] Count Value based on Another Filtered Column

[Anonymous]

Hi guys, 

I have problems with the calculation of one value. My task is the following:

I have:

table: users with

columns: [id, name, createdAt, partnerId, isPartner]
A user can engage other users, they are the partner. If a user has partnerId, it means they have been engaged by another user. isPartner has values 1 or 0.

I need to count users who have partnerId, but only for partners who were created at a specific date range. 

My measure is here:  

allReferals = CALCULATE(
    COUNT(users[id]),
    FILTER(
        'users',
        'users'[partnerId] <> BLANK() 
        && 'users'[partnerId] IN { 
            SELECTCOLUMNS(
                FILTER(
                    'users', 
                    'users'[createdAt] >= CALCULATE(MIN('calendar'[Date]), ALLSELECTED('calendar'[Date]))
                    && 'users'[createdAt] <= CALCULATE(MAX('calendar'[Date]), ALLSELECTED('calendar'[Date]))
                    && 'users'[isPartner] = 1
                ),
            "id2", [id]
            )
        }
    )
)

It doesn't work at all. You can see my sample here: allReferals.pbix 

Thanks in advance!

[dax · Accepted solution]

Hi @Anonymous , 

You could try below measure to see whether it work or not.

Measure 1 = CALCULATE(
    COUNT(users[id]),
    FILTER(
        'users',
        'users'[partnerId] <> BLANK() 
        &&'users'[createdAt] >= MIN('calendar'[Date])&& 
                    'users'[createdAt] <= MAX('calendar'[Date])
                    && 'users'[isPartner] = 1
            
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mhossain]

Hi @Anonymous 

Not sure why you are writing date filter criteria, measure will work already as per your slicer.

Try this

allReferals = CALCULATE(

COUNTROWS(users),users[partnerID]<>BLANK(), users[isPartner]=1)

Please let me know if above works

[amitchandak]

@Anonymous , not very clear.

Like

calculate(count(user[ID]),user[isPartner]=1)

[Anonymous]

calculate(count(user[ID]),user[isPartner]=1)

this will return as the count of users who are partners.

I need users who were engaged by partner-users (they have partnerId != 0) (partner-users should be created from 08/11/2020 to 08/12/2020 for example).