Forum Discussion
Measures vs Custom Columns
Hi There.
I have a very unique problem and I was hoping you could help.
I have a project that requires me to filter the some data with very specific queries. This filters are very specific and contain a lot of diferent criteria, which would make it useless to filter through slicers. Hence, using measures was a easier solution for me.
Let's say I have 5 different categories (which require filtering) and I have created a measure for each category.
One alternative that was provided to me was to use Custom Columns and create them with the filters I need for each Category, but this would mess up with my model too much, and since several people would use the SM as well this is not ideal for our purposes.
Here is an example of my measure.
Please, let me know if you have any questions, I might not have done the best job explaining myself. Thanks.
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.
4 Replies
This seems like a good use case for Calculation Groups.
You would create calculation group items like this:
// Calculation Item: Category 1 CALCULATE ( SELECTEDMEASURE(), KEEPFILTERS ( vw_dimsalesdivision[SalesDivision] IN { "2000:20:RW", "2000:30:RW", "4000:20:RW", "4000:30:RW" } ), KEEPFILTERS ( vw_factsalesorderdetail[SalesDocumentType] IN { "TA", "ZCD", "ZSMP", "ZASH" } ), KEEPFILTERS ( vw_dimsalesgroup[SalesOffice] IN { "200", "25", "52", "53" } ), KEEPFILTERS ( vw_factsalesorderdetail[RejectionReason] = BLANK () ) )Then you can apply that calculation to any measure.
Resources:
Introducing Calculation Groups - SQLBI
Create calculation groups in Power BI - Power BI | Microsoft Learn
Unlocking the Power of Calculation Group - Beyond ... - Microsoft Fabric Community
Hi albertowong
You can use field parameters on measures which will return the value only for the measure selected.
Let report readers use field parameters to change visuals
Also, SUMX is unnecessary in yoru formula. if [Total Sales Orders $] is a column
CALCULATE( SUM (vw_factsalesorderdetail[Total Sales Orders $]), KEEPFILTERS(vw_dimsalesdivision[SalesDivision] IN {"2000:20:RW", "2000:30:RW", "4000:20:RW", "4000:30:RW"}), KEEPFILTERS(vw_factsalesorderdetail[SalesDocumentType] IN {"TA", "ZCD", "ZSMP", "ZASH"}), KEEPFILTERS(vw_dimsalesgroup[SalesOffice] IN {"200", "25", "52", "53"}), KEEPFILTERS(ISBLANK(vw_factsalesorderdetail[RejectionReason])) )if a measure
CALCULATE( [Total Sales Orders $], KEEPFILTERS(vw_dimsalesdivision[SalesDivision] IN {"2000:20:RW", "2000:30:RW", "4000:20:RW", "4000:30:RW"}), KEEPFILTERS(vw_factsalesorderdetail[SalesDocumentType] IN {"TA", "ZCD", "ZSMP", "ZASH"}), KEEPFILTERS(vw_dimsalesgroup[SalesOffice] IN {"200", "25", "52", "53"}), KEEPFILTERS(ISBLANK(vw_factsalesorderdetail[RejectionReason])) )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. 🙂
