Measure is showing values for all years...despite having a date table filter

[Anonymous]

Hello Community  -  I have a date table with a relationship to my date shipped column in my shipments table.  

When I put the measure below in a table, with the ship date as one of the columns, the measure shows All dates in the dataset, rather than whatever the date table is filtered to  (the measure seems to be ignoring my date relationship).  

Is there a way to force this so that the measure respects what is in the slicer (from the date table)?   

Measure  =  (IF([Shipped Value] + ([Non-Trade Shipments] + [Interco Shipments]) + [Deferred Sum] + [Accounting Deferred Revenue]  = 0,0, [Shipped Value] + ([Non-Trade Shipments] + [Interco Shipments]) + [Deferred Sum] + [Accounting Deferred Revenue])

[Anonymous]

@Greg_Deckler   This worked pefectly.   Could you give a simple explanation as to what made this work?

[Greg_Deckler]

@Anonymous Well, the problem is that the original measure always returned a value, either 0 or the actual number essentially. So, I suspect that since you had a measure that always returned a value it forced the table/matrix to display that row. It's sort of weird behavior but I've seen it happen. Would really have to better understand your data model and such to be perfectly certain but that's the crux of it. Any time you have a measure that always returns a value then you can end up with some wonkiness like what you were seeing.

[Anonymous]

@Greg_Deckler    That worked perfectly Greg!  Would you mind just a simple explanation as to why it did?

[Greg_Deckler]

@Anonymous Tough to say with the information provided. I assume that all of those things are references to other measures and the issue probably lies with the underlying formulas for those measures.

Alternatively, you might have an issue in your relationship, for example, the date shipped column might contain times other than 00:00:00 and thus it is possible that no rows in your date table are actually matching any of the rows in your fact table.

[Anonymous]

@Greg_Deckler   I can put each of these measures on a table and filter by date with no issues.   But putting the AMER / APAC measure into the table will cause all years to show up in the table, even if the date slicer just is set to 2022.    The core measure used in all of them comes from the "shipped value" measure, and all of the columns are from the same table  (except of course the date table).   

The issue seems to be with having any dates on the table that are not in a relationship with the date table.   For example, if I have the AMER measure in this table with Order Date, it ends up showing all order dates even though I have the date table filter set to 2022 and even though I have a column from the date table in the table itself (month & year).    If I remove that measure, the date filter/slicer works again.   So something about the AMER / APAC measure is causing all dates in the table to show up and is ignoring the relationship of between my date table and my shipping table.   

Deferred Sum =

CALCULATE (

    [Shipped Value] *-1,

    ShippedOrdersALL_Query[Deferred] IN { TRUE() }

)

Accounting Deferred Revenue =

//Includes Deferred Revenue//

 CALCULATE (

    [Shipped Value],

    USERELATIONSHIP ('Dim_Date Table'[Date],ShippedOrdersALL_Query[RevenueDate]),KEEPFILTERS(ShippedOrdersALL_Query[Deferred])

)

Non-Trade Shipments =

CALCULATE (

    [Shipped Value] *-1,

    ShippedOrdersALL_Query[Market]

        IN { "CORP" }

)

[Greg_Deckler · Accepted solution]

@Anonymous Try this:

Measure  =  
  VAR __Value = [Shipped Value] + ([Non-Trade Shipments] + [Interco Shipments]) + [Deferred Sum] + [Accounting Deferred Revenue]
RETURN
  IF(__Value = 0 || __Value = BLANK(),BLANK(), __Value)