Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
Hi @Anonymous
Not sure why you are writing date filter criteria, measure will work already as per your slicer.
Try this
@Anonymous , not very clear.
Like
calculate(count(user[ID]),user[isPartner]=1)
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).