Forum Discussion
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.
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
bc2022 , Try Field parameters, Make sure none of the measures using +0 or handling of blank or the below option should not be used
Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllEThank 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.
