Help with disconnected slicer required

[Anonymous]

Hi guys.

I'm relatively new to disconnected slicers, but I get how they work and have been able to build a timeframe slicer that does what I want it to do. However, whilst all of my values show up correctly in tables and charts, showing month-by-month figures for the selected timeframe, I cannot get the measure to produce a total. The Card metric just returns a blank.

 Any help would be greatly appreciated!

I'm using two tables - Dates (with all my dates and Target Costs) and DateSlicer (my disconnected slicer table). The Dates table has a Relative Month Index. DateSlicer contains a 'MinValue' column, showing the earliest RMI that I need to pull in, and a 'MaxValue' column, showing the latest RMI value. I have created the following slicer measures:

SlicerMinValue = MAX(DateSlicer[MinValue])

SlicerMaxValue = MAX(DateSlicer[MaxValue])

I have then created a measure to check whether the values in the RMI column fall between the Min and Max value from the slicer. All of the Target Cost entries that fall between the two values should then be pulled out and summed, using the following formula:

TargetCostMeasure = IF(AND(MAX(Dates[RMI])>=DateSlicer[SlicerMinValue],MAX(Dates[RMI])<=[SlicerMaxValue]), sum(Dates[Target Net Costs]), BLANK())

The table looks like it should do when I adjust the slicer, so it's clearly pulling out the right values, but it's just not summing them. Any idea what I'm doing wrong??

[SqlJason · Accepted solution]

It is hard to comment without seeing the model and data, but for now, can you try using an iterator? Something like the formula below

TargetCostMeasure =
SUMX (
VALUES ( Dates[Month] ),
IF (
AND (
CALCULATE ( MAX ( Dates[RMI] ) ) >= DateSlicer[SlicerMinValue],
CALCULATE ( MAX ( Dates[RMI] ) ) <= [SlicerMaxValue]
),
CALCULATE ( SUM ( Dates[Target Net Costs] ) ),
BLANK ()
)
)

(and the reason is that there is no context for the measure in that visual, like @Greg_Deckler said.). 

[Anonymous]

Ah, that's done the trick!

I hadn't thought of using SUMX() in this context - but I can see what it's doing. Thank you.

[Greg_Deckler]

I'm guessing that the reason is that in the context of the visual, there is no date reference to check min and max on and thus there is nothing being pulled back in terms of data and thus blank.