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
IanR
Helper III
Helper III

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
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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.

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

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.

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.