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
bc2022
Frequent Visitor

dynamically showing date range in Matrix

Hello,

 

Is there a way to dynamically adjust the date range based on the options listed in switch? My use case is I want to show metrics value within certain year range for different categories in matrix. The problem is each individual category may have different year range data. Currently, the report shows blank cells for those dates without value. So my goal is for below screenshot, the matrix only shows columns having data which start from column Jul-21.

 

1.png

 

The dax I used is :

test = SWITCH(
SELECTEDVALUE(Criteria[Criteria]),
"Option1", FORMAT(SUM(Rating[Value]),"General Number"),
"Option2",FORMAT(SUM(Rating[Value]),"0%"),
"Option3",FORMAT(SUM(Rating[Value]),"0%")
)

 

I can retrieve the start date and end date of each category dataset by using firstdate() and lastdate() function. But no sure how to incorporate them into above dax without breaking existing logics.

 

Many thanks.

 

2 REPLIES 2
amitchandak
Super User
Super User

@bc2022 , Try Field parameters, Make sure none of the measures using +0 or handling of blank or the below option should not be used 

 

Show item with No data new.png

 

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 

Thank you for your suggestion. 

I tried this approach but it seems it can't help with my issue. When I created a field parameter for the category field, it just gave me a category slicer without any available options (in my case, it did not list option1,option2, option3 which user can choose) . Also, it seems there is no place I can restrict the date range I mentioned in my question.

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.