Measure is ignoring all filters and slicers

[Anonymous]

Hello  -  I have the following measure in a table.   The measure tells me if a shipment is on time or late (not counting weekends), and it works perfectly fine for that.    However, it ignores any type of filter or slicer either from another visual or from the filter menue.    The visuals below show how it looks without, and with this measure applied.   

Any idea how to fix this or create a measure/column without this issue?  

New AR On Time Late Measure =

VAR _due = SELECTEDVALUE('Flu Shipped'[Due Date])

VAR _shipped = SELECTEDVALUE('Flu Shipped'[Date Shipped])

VAR _datediff =

    CALCULATE(COUNTROWS(DateTable),FILTER(DateTable,DateTable[Date]>=_due&&DateTable[Date]<=_shipped&&DateTable[Weekday2]<6))

    RETURN IF(_datediff<=3,"On Time","Late")

This is how it should look without the measure applied.

This is with the measure applied:

[v-yiruan-msft]

Hi @Anonymous,

I just updated the formula of your measure, please try this:

New AR On Time Late Measure =

VAR _due = SELECTEDVALUE('Flu Shipped'[Due Date])

VAR _shipped = SELECTEDVALUE('Flu Shipped'[Date Shipped])

VAR _datediff = if(_shipped<_due,"on time",if(CALCULATE(COUNTROWS(DateTable),FILTER(DateTable,DateTable[Date]>=_due&&DateTable[Date]<=_shipped&&WEEKDAY([Date],1)<6))<=3,"On Time","Late"  ) )

RETURN _datediff

Best Regards

Rena

[Anonymous]

Hi Rena, 

I was mistaken earlier -  your measure is not properly assigning Late  or   On Time  (taking into account only weekdays).  

Also, after playing around with your formula, it seems the reason it does not respond to the Date Table filters (such as Year, Month, Day) is that the Selected Values are on the date shipped and due date.   If I change these to Date Table,Date then my applied filters actually work.    Your example where you selected on specific date is not workable for my situation as I will only filter at the Year, Month, Week or Day level using the filters from chart visual (using the date table values of Month, Week, Day, etc).   

Can the formula be adjusted?  

[v-yiruan-msft]

Hi @Anonymous ，

Whether your problem has been resolved?

Best Regards

Rena

[v-yiruan-msft]

Hi @Anonymous,

I made a little mistake(see the part with red font) about the judgment on whether it is a weekend in my previous formula. Here is the corrected formula:

New AR On Time Late Measure =

VAR _due = SELECTEDVALUE('Flu Shipped'[Due Date])

VAR _shipped = SELECTEDVALUE('Flu Shipped'[Date Shipped])

VAR _datediff = if(_shipped<_due,"on time",if(CALCULATE(COUNTROWS(DateTable),FILTER(DateTable,DateTable[Date]>=_due&&DateTable[Date]<=_shipped&&WEEKDAY([Date],2)<6))<=3,"On Time","Late"  ) )

RETURN _datediff

So currently you want to use the date hierarchy of Date table to filter the data ? I don't understand why the date of this table is used for data filtering... Anyway, could you please try to updated the above formula according to your date filtering requirements and check if it can achieve your requirements.

Best Regards

Rena

[Anonymous]

Hi Rena  -   Several people have tried to help (which I really appreciate!) but so far none of the solutions are working, including yours. 

Here is a very basic calculated column that does at least allow for the table to be properly filtered. 

AR Channel OnTime or Late = if('Flu Shipped'[Date Shipped] - 'Flu Shipped'[Due Date]<=3, "On Time","Late")

Your formula works if I put on a slicer for either the date shipped or the due date.

But in my chart visual, I am using the Year, Month, and Day from my Date Table.

The measure you suggested does not respond to date filters from the date table.

For example, the chart visual has Year Month Day hieracrchy, and when you drill down this should also filter the table to the left.

But the measure is ignoring it.

[amitchandak]

Try like

New AR On Time Late Measure =
VAR _due = minx(allselected('Flu Shipped'),'Flu Shipped'[Due Date])
VAR _shipped = maxx(allselected('Flu Shipped'),'Flu Shipped'[Date Shipped])
VAR _datediff =
    CALCULATE(COUNTROWS(DateTable),FILTER(DateTable,DateTable[Date]>=_due&&DateTable[Date]<=_shipped&&DateTable[Weekday2]<6))
    RETURN IF(_datediff<=3,"On Time","Late") 

[Anonymous]

Hi Amit, 

Thank you for the help...but this measure is still ignoring my date table filters.   

The Date Shipped and Due Date are both in the Flu_Shipped table....and the relationship is between Date Shipped and Date on the date table.   

If I use a very basic calculated column like this, it allows my chart visual to properly cross filter my table.   Not sure why this works, but the measures do not.   For some reason, there is something in the Measure that is causing it to ignore the Year, Month, and Day from the date table.    I have those elements on my chart visual, and when I drill down...the table should correspond to whatever the applied filter is at the current hierarchy.    But when I apply the measure it does not respond at all to any filters from the Date Table.    This basic caclulated column does though...

NON AR Channel On Time or Late = IF('Flu Shipped'[Date Shipped] - 'Flu Shipped'[Due Date]<=3, "On Time","Late")