Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |