Forum Discussion
Measures vs Custom Columns
Hi albertowong,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to danextian, AlexisOlson, for his inputs on this thread. Here I provided workaround that might be resolve the issue quickly.I completely understand the challenge you are facing. You are right that using slicers with measures directly is not possible, and manually recreating pages for each category can quickly become unmanageable.
Based on what you are shared, a potential solution would be to use a "disconnected table" approach. Here's how it could work in theory:
- Create a disconnected Category table (e.g., Category Name: "Category 1", "Category 2", etc.). This table won’t be related to your model.
- Add a slicer based on that Category table so the user can choose a category easily.
- In your measure logic, use the selected value from the disconnected Category table to dynamically apply the appropriate filters within a SWITCH statement. This way, when the user selects a category from the slicer, the corresponding logic is triggered, and all the complex filtering gets applied behind the scenes.
This approach will give users a simple slicer interface to choose the category, while the measure dynamically handles the logic based on the selection no need for custom columns or maintaining multiple pages.
Kindly refer to the below mentioned documents for better understanding:
SWITCH function (DAX) - DAX | Microsoft Learn
SELECTEDVALUE function - DAX | Microsoft Learn
CALCULATE function (DAX) - DAX | Microsoft Learn
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.
Hi albertowong,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to danextian, AlexisOlson, for his inputs on this thread. Here I provided workaround that might be resolve the issue quickly.
I completely understand the challenge you are facing. You are right that using slicers with measures directly is not possible, and manually recreating pages for each category can quickly become unmanageable.
Based on what you are shared, a potential solution would be to use a "disconnected table" approach. Here's how it could work in theory:
- Create a disconnected Category table (e.g., Category Name: "Category 1", "Category 2", etc.). This table won’t be related to your model.
- Add a slicer based on that Category table so the user can choose a category easily.
- In your measure logic, use the selected value from the disconnected Category table to dynamically apply the appropriate filters within a SWITCH statement. This way, when the user selects a category from the slicer, the corresponding logic is triggered, and all the complex filtering gets applied behind the scenes.
This approach will give users a simple slicer interface to choose the category, while the measure dynamically handles the logic based on the selection no need for custom columns or maintaining multiple pages.
Kindly refer to the below mentioned documents for better understanding:
SWITCH function (DAX) - DAX | Microsoft Learn
SELECTEDVALUE function - DAX | Microsoft Learn
CALCULATE function (DAX) - DAX | Microsoft Learn
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.
Hi there. Thanks everyone for your contributions.
I solved my issue following v-kpoloju-msft suggestion.
I created a disconnected table with one column and in that column I listed my Categories.
Then I created a slicer that allowed me to select a Category.
Then I created a measure that would trigger all the filters if the slicer matched a selected category.
And I created a variable inside the measure so that I don’t have a measure for each category.
Finally, I added the Switch function for it to retrieve the variable based on the slicers selection.
Here is a sample of the formula.
Daily Sales Orders =
Var _Selected = SELECTEDVALUE('Categories'[Category])
Var Category_1 = CALCULATE([Total Sales Orders $],
keepfilters(Table[Field] in {"Value1" , "Value2", " Value3", " Value4"})
keepfilters(Table[Field] in {"Value1" , "Value2", " Value3", " Value4"})
Var Category_2 = CALCULATE([Total Sales Orders $],
keepfilters(Table[Field] in {"Value1" , "Value2", " Value3", " Value4"})
keepfilters(Table[Field] in {"Value1" , "Value2", " Value3", " Value4"})
-- and so on for as many categories I needed, with as many filters as they need each one of them --
Return
Switch(
True()
, _Selected = "Category_1", Category_1
, _selected = "Category_2", Category_2
)
So at the end you helped me set up my report exactly as I needed it. Thank you so much. 🙂