Scratching my head over disconnected slicer

[masplin]

Hi. i'm obviously doing something dim.

I have calculated column that sums the quantity on a table of invoices for different contracts to see how much has been consumed under each one. We want to be able to run this calculation at certain dates like EOM. Without any date filter it will always give the current balance not the one at EOM. Once the calculated column is correct then I calculate the value remaining on each contract and then have a measure that sums these quantities. This measure is in a pivot table.

Everything works fine when the date (the bit after <=) was set to a fixed date I put in a linked table and referenced in the formula, but that involved refreshing the linked table each time to change the date

I then thought I could give the user the ability to select the date using a disconnected slicer. i created a table of EOM Dates and linked them to the model. I created a slicer linked to the pivot and edited the calculated column as below, where PlatBSDate[Platinum BS Date] is the value chosen in the slicer.

=CALCULATE(                        SUM('Posted Document Line'[Quantity]),
                        FILTER(
                                      'Posted Document Line',
                                      'Posted Document Line'[Contract line ID]='Contract_Service Discount'[Contract Line ID] &&
                                      'Posted Document Line'[Posting Date]<=MAX(PlatBSDate[Platinum BS Date])
                                    )
                         )

However nothing happens when I change the slicer date. Is this because disconnected slicer can only work if the whole calculation is in a measure and can't affect calculated columns?

Thanks for any advice

Mike

 

[akice]

A disconnected slicer is just that. It is a slicer based on a table column of which the table is not related to any other table in the data model. (Which is why it is a bit confusing when you wrote " i created a table of EOM Dates and linked them to the model." There is no requirement it only be used in a measure.

Once you have the disconnected table, you just have to make sure the slicer is associated with a particular pivot. Easiest way is while active cell is in pivot, go to the Field List and right click on column and do a "Add as Slicer".

Otherwise while active cell is in pivot, you can go to Analyze -> Filter Connections to make sure your slicer is associated with pivot. (Excel 2016).

If it is not associated with pivot, Excel won't send pivot update event when slicer selection changes.

 

[masplin]

I think you misunderstood the question. When I said I linked my disconnected table to the model I meant I added it as linked table. I then created a measure [Plat BS Date=MAX(disconnected table dates) i.e. the one that has been chosen in the slicer. This works correctly as if I do a pivot and put my measure in it and click a date in my disconnected slicer it updates as expected.

all the examples i see the measure being driven by the slicer is fed into another measure. i am trying to feed it into a calculated column which might not be possible? The question is do I need to create a measure which involves me working out how to use ADDCOLUMNS instead of using calculated columns

Thanks
Mike

 

[akice]

Ah..ok.

Calculated columns are computed at data refresh time, not at run time. So no, you can't use a slicer to change values in a calculated column. Slicers can only be used in a measure. (Though measure could be programmed to SUM different calculated columns in a table based on slicer selection.)

 

[masplin]

Thanks for confirming.

 

[Matt Allington]

He a read of my article here Calculated Columns vs Measures in DAX - Excelerator BI

 

[masplin]

Yup time to learn to write some more complex measures