Measures vs Custom Columns

[albertowong]

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. 

Category 1 = CALCULATE(SUMX(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(vw_factsalesorderdetail[RejectionReason] = BLANK()))

I have a different query for each category and some are quite large! If the end user wanted to filter down these at the report level it would take a lot and they would need technical knowledge on how these categories are bulit. My current solution is to create similar pages for each measure (each category), but i'm wondering if you have any solutions for this problem. 

Basically what I want is to be able to use a slicer where I can select each category and that all the filters apply automatically, and since I cannot use slicers with measures that's not possible right now. 

Please, let me know if you have any questions, I might not have done the best job explaining myself. Thanks. 

[v-kpoloju-msft · Accepted solution]

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.

[albertowong]

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. 🙂 

[danextian]

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]))
)

[AlexisOlson]

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