Issue with ALL

[JimJim]

Hi Guys,

I have the following measure:

Total > 2 days = 
VAR MinDate = MIN(DateMaster[Date])
RETURN
    COUNTROWS(FILTER('Service Appointment',
    'Service Appointment'[IsWIP]=1 && 
    'Service Appointment'[WIP Days] > 2 &&
    'Service Appointment'[Actual Start Time] > MinDate))

At the top of the page there is a single date slicer where a user selects a month.

If the slicer value is Sep 2020, I want the measure to calculate all rows where the Actual Start Date > 1 Sep 2020.

I attempted to add ALL to my measure, like so:

Total > 2 days = 
VAR MinDate = MIN(DateMaster[Date])
RETURN
    COUNTROWS(FILTER(ALL('Service Appointment'),
    'Service Appointment'[IsWIP]=1 && 
    'Service Appointment'[WIP Days] > 2 &&
    'Service Appointment'[Actual Start Time] > MinDate))

And although this returns the correct number of rows, when I add it to a matrix and it shows me the total number on each row, like so:

 

Note: I am not able to change the slicer to show only data after the selected date, as other visuals use the same slicer.

[v-zhenbw-msft · Accepted solution]

Hi @JimJim ,

We can create a date table that has no relationship to meet your requirement.

1. Create a date table to be a slicer. You need to add month name column and month number column,

Date = CALENDAR("2020/8/1","2020/12/31")

2. Then we can create a measure like this,

Measure = 
var _select = SELECTEDVALUE('Date'[Month])
var _mindate = CALCULATE(MIN('Date'[Date]),FILTER('Date','Date'[Month]=_select))
return
COUNTROWS(
    FILTER(ALLSELECTED('Service Appointment'),'Service Appointment'[Start Date]>_mindate))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[JimJim]

Hi @v-zhenbw-msft ,

Thank you so much. This does the job 🙂

[JimJim]

My old measure is now working also, turns out all I needed to do was remove the relationships from my DateMaster table. 

[amitchandak]

@JimJim , Either have an independent date filter and use first formula only .

Assuming Actual start time joined with Date master

Total > 2 days = 
VAR MinDate = MIN(DateMaster[Date])
RETURN
    COUNTROWS(FILTER(('Service Appointment'),
    'Service Appointment'[IsWIP]=1 && 
    'Service Appointment'[WIP Days] > 2 ,
    filter(all(DateMaster),DateMaster[Date]> MinDate))

[JimJim]

@amitchandak 

I edited your DAX to this

Total > 2 days = 
VAR MinDate = MIN(DateMaster[Date])
RETURN
COUNTROWS(FILTER('Service Appointment',
'Service Appointment'[IsWIP]=1 && 
'Service Appointment'[WIP Days] > 2 &&
FILTER(ALL(DateMaster), DateMaster[Date] > MinDate )))

But getting error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'

I really appreciate your help with this.