Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ruthhacche
Helper III
Helper III

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

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).

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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
Community Support
Community Support

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.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors