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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
csinusa
Frequent Visitor

Optional filter in DAX Summarize query

Hi, 

Trying to see how to write the query implement if the filter has value selected or if its not selected (meaning ALL is selected) then how to apply or not apply the filter.

 

Example 

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Brand],
    'Date'[Calendar Year],
    TREATAS ( { "CY 2008", "CY 2009" }, 'Date'[Calendar Year] ),  <-- If user doesnt select any of the year then I dont want to apply this filter 
    TREATAS ( { "Red", "Blue" }, 'Product'[Color] ),
    "Amount", [Sales Amount],
    "Qty", SUM ( Sales[Quantity] )
)
 
1 ACCEPTED SOLUTION
3 REPLIES 3
csinusa
Frequent Visitor
amitchandak
Super User
Super User

@csinusa , You can not use slicer value in calculated table.

 

If this a var of meausre you can try like

 

TREATAS ( allselected('Date'[Calendar Year]), 'Date'[Calendar Year] )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Actually I m creating paginated version of report using powebi desktop dataset and using DAX queries to get data. My desktop report has no issue selecting/ignoring parameters from dropdownlist but my paginated report makes me select values in all parameters. 

So if I have customer / locations / sites , it is possible the user only want to run the paginated report by customer but if decided to run for one location, then he will select location as parameter otherwise we want to pass as null or all for both location and sites parameter.

 

Thats what the issue how to set the filters in DAX when passed and not set when not passed. 

The customer, location and sites are all in dropdownlist

parameters.JPG 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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