cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanR Member
Member

Trouble with CALCULATE, FILTER and ALL

I’m probably doing something very stupid here but I can’t get a simple CALCULATE with FILTER to behave the way I want it to. I’m working with CRM data and I’m trying to create a measure that shows the number of opportunities that were open on any selected date. The logic for this is (I hope) relatively basic. The part that I’m finding difficult is circumventing the relationship between the opportunity and date tables. These are linked by the CreatedOn column in the opportunities table. The filter on the page that the measure will be used in has a start and end date. I need to stop this affecting the opportunity table in my measure then apply the filters that I need in order to find the opportunities that were open on the selected end date. I can do this using ALL(Opportunities) but the measure then does not respond to the row that it is in when placed in a matrix.

 

Opps Open on Date - Working But Can't Filter in Matrix = 
CALCULATE(COUNTROWS(OpportunitySet),
          FILTER(ALLEXCEPT(OpportunitySet, OpportunitySet[OwnerId.Name]),
                 OpportunitySet[CreatedOn] < DATE(2017,01,01)
                 && ( OpportunitySet[ActualCloseDate] >= DATE(2017,01,01)
                      || OpportunitySet[ActualCloseDate] = BLANK() )      
          )
)

I tried to restrict the ALL to just the CreatedOn column but this gives errors of the form ‘A single value for the column ActualCloseDate cannot be determined’.

 

Opps Open on Date - Not Working at all = 
CALCULATE(COUNTROWS(OpportunitySet),
          FILTER(ALL(OpportunitySet[CreatedOn]),
                 OpportunitySet[CreatedOn] < DATE(2017,01,01)
                 && ( OpportunitySet[ActualCloseDate] >= DATE(2017,01,01)
                      || OpportunitySet[ActualCloseDate] = BLANK() )      
          )
)

I suspect this second version is not working because ALL(OpportunitySet[CreatedOn]) returns a table with just the CreatedOn fields in it. I’ve tried ALLEXCEPT(OpportunitySet[Owner]) but that gives the same result for all rows as well – which I really don’t understand.

 

I’m sure I’m missing something obvious.

5 REPLIES 5
Super User
Super User

Re: Trouble with CALCULATE, FILTER and ALL

@IanR  its probably silly but do you need another set of bracket to encapsulate the whole 'where' statement as ive done below?  worth a check or do you need another filter statement?

 

Opps Open on Date - Not Working at all =
CALCULATE (
    COUNTROWS ( OpportunitySet ),
    FILTER (
        ALL ( OpportunitySet[CreatedOn] ),
        (
            OpportunitySet[CreatedOn] < DATE ( 20170101 )
                && (
                    OpportunitySet[ActualCloseDate] >= DATE ( 20170101 )
                        || OpportunitySet[ActualCloseDate] = BLANK ()
                )
        )
    )
)


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
IanR Member
Member

Re: Trouble with CALCULATE, FILTER and ALL

Hi vanessafvg,

 

Thanks for responding so quickly. Unfortunately when I tried your version I got the same result; squiggly red lines under the ActualCloseDate  part of OpportunitySet[ActualCloseDate] and this error: 'A single value for column 'ActualCloseDate' in table 'OpportunitySet' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

 

Ian

v-xjiin-msft Super Contributor
Super Contributor

Re: Trouble with CALCULATE, FILTER and ALL

Hi @IanR,

 

Could you please share us your pbix file if possible? So that we can dig deeper and make some tests.

 

Thanks,
Xi Jin.

IanR Member
Member

Re: Trouble with CALCULATE, FILTER and ALL

Hi v-xjiin-msft,

 

As this comes from CRM there's a lot of confidential data in there. I'll try and create an anonymized version. This will take a few days due to work constraints.

 

Are you from Microsoft?  Are you able to confirm or otherwise that I have not made a real schoolboy error in the code? By yesterday lunchtime I was doubting everything I thought I knew about DAX.

 

Thanks
Ian

v-xjiin-msft Super Contributor
Super Contributor

Re: Trouble with CALCULATE, FILTER and ALL

Hi @IanR,

 

Sorry for delay.

 

So ALL(OpportunitySet[CreatedOn]) only returns a table with just the CreatedOn fields in it. Then maybe you can try ALL(OpportunitySet) directly.

 

 

Opps Open on Date - Not Working at all =
CALCULATE (
    COUNTROWS ( OpportunitySet ),
    FILTER (
        ALL ( OpportunitySet ),
        OpportunitySet[CreatedOn] < DATE ( 2017, 01, 01 )
            && (
                OpportunitySet[ActualCloseDate] >= DATE ( 2017, 01, 01 )
                    || OpportunitySet[ActualCloseDate] = BLANK ()
            )
    )
)

 

By the way, is this CreateOn start date and ActualCloseDate end date? If so, should the condition be OpportunitySet[CreatedOn] > DATE ( 2017, 01, 01 ) && OpportunitySet[ActualCloseDate] <= DATE ( 2017, 01, 01 )? 

 

Thanks,
Xi Jin.