Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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 ( 2017, 01, 01 )
&& (
OpportunitySet[ActualCloseDate] >= DATE ( 2017, 01, 01 )
|| OpportunitySet[ActualCloseDate] = BLANK ()
)
)
)
)
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |