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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
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
LinkedIn

Hi Owen,

 

Many thanks for your answer, it works perfectly fine.

 

Regards

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.