Filtering based on multiple parameters/thresholds

[daosborne]

Hi,

I want to filter one table based on mulitple thresholds. See examples below:

Data table:

Expense amount     Category

250                            Airline

500                            Airline
150                            Airline

300                            Entertainment

1000                          Entertainment

Right now i have one parameter, that for example, i can enter in 200 in a slicer, and the table will update to show me expenses that are over $200. Filtered view below:

Expense amount     Category

250                            Airline

500                            Airline

300                            Entertainment

1000                           Entertainment

What i want to be able to do is to have thresholds, that the user can update directly through a slicer on the page, for each expense category.
For example, I want to only show airline expenses over $200, and entertainment expenses only over $500. Below is what the result would be:

Expense amount     Category

250                            Airline

500                            Airline

1000                          Entertainment

Does anyone know how to do this? Any help is appreciated. Thank you!

[v-alq-msft · Accepted solution]

Hi, @daosborne 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Data:

AirExpense1(a calculated table):

AirExpense1 = GENERATESERIES(1,MAX(Data[Expense amount]),1)

EntertainmentExpense2(a calculated table):

EntertainmentExpense2 = GENERATESERIES(1,MAX(Data[Expense amount]),1)

You may create a measure like below.

Visual Control = 
var _category  = SELECTEDVALUE(Data[Category])
var _expense = SELECTEDVALUE(Data[Expense amount])
return 
IF(
    ISFILTERED(Category[Category]),
    IF(
        HASONEVALUE(Category[Category]),
        IF(
            SELECTEDVALUE(Category[Category]) = "Airline"&&HASONEVALUE(AirExpense1[Value])&&_category="Airline"&&_expense>=SELECTEDVALUE(AirExpense1[Value]),
            1,
            IF(
                SELECTEDVALUE(Category[Category])="Entertainment"&&HASONEVALUE(EntertainmentExpense2[Value])&&_category="Entertainment"&&_expense>=SELECTEDVALUE(EntertainmentExpense2[Value]),
                1,0
            )
        ),
        IF(
            OR(
                _category="Airline"&&_expense>=SELECTEDVALUE(AirExpense1[Value]),
                _category="Entertainment"&&_expense>=SELECTEDVALUE(EntertainmentExpense2[Value])
            )&&SELECTEDVALUE(AirExpense1[Value])<>BLANK()&&SELECTEDVALUE(EntertainmentExpense2[Value])<>BLANK(),
            1,0
        )
    ),
    1
)

Finally you may put the measure 'Visual control' in the corresponding visual level filter to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@daosborne , what if parameter should help you

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Also, refer Segmentation
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/

[daosborne]

@amitchandak amitchandak
thanks for the quick response! Can you provide more context on using multiple what if parameters? Right now, i have one what if parameter, that will help filter the table based on if the expense exceeds the amount i enter in the parameter. I'm having trouble creating multiple what if parameters, one for the airline threshold, one for the entertainment threshold, and having the table update accordingly.