Altering evaluation context - return table ignoring current filter context in measure

[Tyler_Penkofski]

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 !

[amitchandak · Accepted 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
        ) 
    )

[Tyler_Penkofski]

Thank you !  This appears to worked, as now I am seeing some logical results from the measure:

[Greg_Deckler]

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.

[Tyler_Penkofski]

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]: