Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tyler_Penkofski
Frequent Visitor

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

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: 

 

image.png

 

Any help would be greatly appreciated !

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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
        ) 
    )

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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:

 

image.png

Greg_Deckler
Super User
Super User

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

image.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.