Add a slicer to use in a cube formula with no pivot table

[ruthhacche]

If you are writing cube formulas from scratch to access data from a power BI dataset - how do you add a slicer to act within those formulas.

Background to what I am asking:  I have been teaching myself cube formulas starting with a pivot attached to my powerBi dataset. I created a pivot, added some slicers fromt he pivot table tab, then converted the pivot to OLAP formulas.  I have then created lots more cube formulas and there is nothing left of the pivot - other than it's slicers.

All good - but now I want to add another slicer for another field.

Do I have to add a pivot table, add a slicer to that pivot, and then delete that pivot?  Surely not.  I have tried that and can indeed add another slicer and it references the same datset but it is not linked to the other slicers that came from the original pivot  of course - what I mean by this is that the original slicers that are all attached to each other flex their options depending on what each of the other slicers have selected and would not allow conflicting selections - whereas the new slicer lets me select any account name even if it does not eg relate to the account ID already selected.   (And the report connections on the slicer tab button is greyed out).

[OwenAuger · Accepted solution]

Hi @ruthhacche 

First of all, to add slicers when you do not have a PivotTable, go Insert > Filters > Slicer.

Then choose the appropriate connection from "Connections in this Workbook", then select the fields(s) from the list.

To link these slicers to your cube formulas, you will need to include the formula name of the slicers as additional arguments at the end of the cube formulas (formula name is visible under Slicer Settings on each slicer).

As far as cross-filtering of slicers, I don't believe this is possible when there are only cube formulas involved but no PivotTables. A suggestion I have seen here is to create a hidden PivotTable (e.g. on a hidden worksheet), connect the slicers to it, and place appropriate measures in the PivotTable for cross-filtering to occur.

Regards,

Owen

[ruthhacche]

Owen that is a perfect answer.  All I needed thank you.

Intersting that you can only add connected slicers via a pivot - I shall have to remember not to delete the hidden pivot in future so that I can add more slicers at a later date.

[v-xuding-msft]

Hi @ruthhacche ,

I'm not clear about your requirement. Usually we create a disconnected table and use its column in a slicer. Can you please share some sample data and expected results with us? Then we will understand more clearly and solve it quickly. Thanks.