Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I'm looking to the community to see if I can get help understanding how to make the below measure work:
Problem I'm solving for: Count all the Tasks that happened between two dates (defined in a slicer), plus 30 days after the max date.
Current code for Measure:
# of SAs + 30: =
var minDate = MIN(dimDate[Key Date])
var maxDate = MAX(dimDate[Key Date]) + 30
/*var ValidSA = CALCULATETABLE(factTask,
factTask[ActivityDate] >= minDate &&
factTask[ActivityDate] <= maxDate
) */
RETURN
CALCULATE(
[# of SAs:],
FILTER(ALL(factTask[ActivityDate]),
VALUE(factTask[ActivityDate]) >= minDate &&
VALUE(factTask[ActivityDate]) <= maxDate
)
)
Code for [# of SA's] (used in above measure):
# of SAs: = CALCULATE(
COUNTROWS(factTask),
TREATAS(
VALUES(factCampaignMember[Case_Safe_ID__c]),
factTask[WhoId]
)
)
Current ERD:
Any help would be greatly appreciated !
Solved! Go to Solution.
One thing you can try
# of SAs + 30: =
var minDate = MIN(dimDate[Key Date])
var maxDate = MAX(dimDate[Key Date]) + 30
/*var ValidSA = CALCULATETABLE(factTask,
factTask[ActivityDate] >= minDate &&
factTask[ActivityDate] <= maxDate
) */
RETURN
CALCULATE(
[# of SAs:],
FILTER(ALL(dimDate),
VALUE(dimDate[Key Date]) >= minDate &&
VALUE(dimDate[Key Date]) <= maxDate
)
)
One thing you can try
# of SAs + 30: =
var minDate = MIN(dimDate[Key Date])
var maxDate = MAX(dimDate[Key Date]) + 30
/*var ValidSA = CALCULATETABLE(factTask,
factTask[ActivityDate] >= minDate &&
factTask[ActivityDate] <= maxDate
) */
RETURN
CALCULATE(
[# of SAs:],
FILTER(ALL(dimDate),
VALUE(dimDate[Key Date]) >= minDate &&
VALUE(dimDate[Key Date]) <= maxDate
)
)
Thank you ! This appears to worked, as now I am seeing some logical results from the measure:
Can you share sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
I would try getting rid of your VALUE statements I don't understand why you are using those. Also, maybe just ALL of your table instead of specifying a particular column.
Thanks Greg, I removed VALUES() and used a table expression instead of a column expresion in my ALL() statement, which returned some valid values for some records and incorrect values for others.
Ideally [# of SAs + 30] should be equal to or greater than [# of SA's]:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |