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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
CMunteanu
Frequent Visitor

Filtering only when value is selected in slicer

Hello,

 

I have the following code that is part of a bigger measure:

 

 

 

 

Measure ORDER2 =
Var _Product=SELECTEDVALUE('Slicer'[PRODUCT])
Var _Store=SELECTEDVALUE('SlicerStore'[STORE_ID])
Var _Year=SELECTEDVALUE('SlicerYear'[YEAR_ID])
Var _ProductID=CALCULATETABLE(VALUES('ProductDim'[PRODUCT_ID]),'ProductDim'[PRODUCT]=_Product)
Var _StoreID=CALCULATETABLE(VALUES('StoreDim'[STORE_ID]),'StoreDim'[STORE_ID]=_Store)
Var _YearID=CALCULATETABLE(VALUES('YearDim'[YEAR_ID]),'YearDim'[YEAR_ID]=_Year)
Var _table1=CALCULATETABLE(VALUES('OrdersFact'[CUSTOMER_ID]),'OrdersFact'[PRODUCT_ID]=_ProductID, OrdersFact[Store]=_StoreID, 'OrdersFact'[Year]=_YearID)

 

 

 


We have 3 slicers one for Product, Store, Year which correspond to three dimensions related to the Orders Fact. We want to change _table1 variable so that when we don't select a value in the slicer (or select all values), the filter in the CALCULATETABLE expression will not filter rows, that is to say: the filter should apply only when we have a selected value in the corresponding slicer.  What would be the best and most scalable solution that would work for 3 or more filters, taking into account that we may have all combinations of slicers selections (some have a value selected, some not).

 

Thank you !

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hello @CMunteanu 

You could use a function like HASONEFILTER to check whether there is a single value filtered on a the slicer columns.

 

The "negation" of the HASONEFILTER check could then be OR-ed with each of the existing boolean conditions used within CALCULATETABLE for _table1.

 

Using variables to store these additional checks, the result would be something like this:

 

VAR _SlicerProduct_SingleFilter =
    HASONEFILTER ( 'Slicer'[PRODUCT] )
VAR _SlicerStore_SingleFilter =
    HASONEFILTER ( 'SlicerStore'[STORE_ID] )
VAR _SlicerYear_SingleFilter =
    HASONEFILTER ( 'SlicerYear'[YEAR_ID] )
VAR _table1 =
    CALCULATETABLE (
        VALUES ( 'OrdersFact'[CUSTOMER_ID] ),
        'OrdersFact'[PRODUCT_ID] = _ProductID || NOT _SlicerProduct_SingleFilter,
        'OrdersFact'[Store] = _StoreID || NOT _SlicerStore_SingleFilter,
        'OrdersFact'[Year] = _YearID || NOT _SlicerYear_SingleFilter
    )

 

 

I think this would be the safest way to write it. You could also write the conditions like this, assuming there are always at least two values available on the slicers:

 

'OrdersFact'[PRODUCT_ID] = _ProductID || ISBLANK ( _ProductID )

 

 

Does the above code work for you?


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

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hello @CMunteanu 

You could use a function like HASONEFILTER to check whether there is a single value filtered on a the slicer columns.

 

The "negation" of the HASONEFILTER check could then be OR-ed with each of the existing boolean conditions used within CALCULATETABLE for _table1.

 

Using variables to store these additional checks, the result would be something like this:

 

VAR _SlicerProduct_SingleFilter =
    HASONEFILTER ( 'Slicer'[PRODUCT] )
VAR _SlicerStore_SingleFilter =
    HASONEFILTER ( 'SlicerStore'[STORE_ID] )
VAR _SlicerYear_SingleFilter =
    HASONEFILTER ( 'SlicerYear'[YEAR_ID] )
VAR _table1 =
    CALCULATETABLE (
        VALUES ( 'OrdersFact'[CUSTOMER_ID] ),
        'OrdersFact'[PRODUCT_ID] = _ProductID || NOT _SlicerProduct_SingleFilter,
        'OrdersFact'[Store] = _StoreID || NOT _SlicerStore_SingleFilter,
        'OrdersFact'[Year] = _YearID || NOT _SlicerYear_SingleFilter
    )

 

 

I think this would be the safest way to write it. You could also write the conditions like this, assuming there are always at least two values available on the slicers:

 

'OrdersFact'[PRODUCT_ID] = _ProductID || ISBLANK ( _ProductID )

 

 

Does the above code work for you?


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

Hi Owen,

 

Many thanks for your answer, it works perfectly fine.

 

Regards

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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