Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mscla
Helper I
Helper I

Error in the minimum value range slicer for a specific product

Hi everyone,

I don't know if it's a bug in the Power Bi Slicer or if I'm doing something wrong.

I need to create a data slicer to vary the data between the minimum and maximum value for a specific product, so I created a Slicers for him.

 

When I set the maximum value, it removes all contracts from the SQSLL product, but if I set the minimum SQSLL value, it removes all contracts from other products.

mscla_0-1718814334016.png

 

I looked for a solution and didn't find it.

Could someone help me with this please?


Power BI Sales slicer Product.pbix 

 

thanks

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

You only have a single table. Your slicers will overlay (ie both will be applied together) when they filter your data.

 

Use disconnected tables for your slicer and then use measures as visual filters for your tables A and B.

View solution in original post

v-zhouwen-msft
Community Support
Community Support

Hi @lbendlin ,thanks for your quick reply, I will add more.

Hi @mscla ,

As @lbendlin  said, you need to create a disconnected table for slicer.

Please follow these steps:

1.Use the following DAX expression to create a table.

Table = CALCULATETABLE(VALUES('table Sales'[Sales]),'table Sales'[Product] = "CQSSL")

2.Use the following DAX expression to create a measure

Measure = 
VAR _product = SELECTEDVALUE('table Sales'[Product])
VAR _minforSlicer = MIN('Table'[Sales])
VAR _maxforSlicer = MAX('Table'[Sales])
VAR _result = IF(_product <> "CQSSL",
SUM('table Sales'[Sales]),
SUMX(FILTER('table Sales','table Sales'[Sales] >= _minforSlicer && 'table Sales'[Sales] <= _maxforSlicer),[Sales])
)
RETURN _result

3.Final out put

vzhouwenmsft_0-1718860310052.png

vzhouwenmsft_1-1718860340440.png

 

Best Regards,
Wenbin Zhou
If this post helps, please mark me and @lbendlin  as the solution.

 

 

View solution in original post

3 REPLIES 3
mscla
Helper I
Helper I

Thank you very much @lbendlin and @v-zhouwen-msft 

Really appreciate your help, and its really worked!

My goal is to have a slicer for each product that the user can increase or decrease for each product. so, I created a combinedMeasure and its working

mscla_0-1718867095386.png

MeasureCombined =
VAR _product = SELECTEDVALUE('table Sales'[Product])
VAR _result =
    SWITCH(
        TRUE(),
        _product <> "CQPT", SUM('table Sales'[Sales]),
        _product <> "CQSSL", SUM('table Sales'[Sales]),
        _product <> "DELOP", SUM('table Sales'[Sales]),
        SUMX(
            FILTER('table Sales',
                'table Sales'[Sales] >= MIN('TableCQPT'[Sales]) && 'table Sales'[Sales] <= MAX('TableCQPT'[Sales]) ||
                'table Sales'[Sales] >= MIN('TableCQSSL'[Sales]) && 'table Sales'[Sales] <= MAX('TableCQSSL'[Sales]) ||
                'table Sales'[Sales] >= MIN('TableDELOP'[Sales]) && 'table Sales'[Sales] <= MAX('TableDELOP'[Sales])
            ),
            [Sales]
        )
    )
RETURN _result
 
v-zhouwen-msft
Community Support
Community Support

Hi @lbendlin ,thanks for your quick reply, I will add more.

Hi @mscla ,

As @lbendlin  said, you need to create a disconnected table for slicer.

Please follow these steps:

1.Use the following DAX expression to create a table.

Table = CALCULATETABLE(VALUES('table Sales'[Sales]),'table Sales'[Product] = "CQSSL")

2.Use the following DAX expression to create a measure

Measure = 
VAR _product = SELECTEDVALUE('table Sales'[Product])
VAR _minforSlicer = MIN('Table'[Sales])
VAR _maxforSlicer = MAX('Table'[Sales])
VAR _result = IF(_product <> "CQSSL",
SUM('table Sales'[Sales]),
SUMX(FILTER('table Sales','table Sales'[Sales] >= _minforSlicer && 'table Sales'[Sales] <= _maxforSlicer),[Sales])
)
RETURN _result

3.Final out put

vzhouwenmsft_0-1718860310052.png

vzhouwenmsft_1-1718860340440.png

 

Best Regards,
Wenbin Zhou
If this post helps, please mark me and @lbendlin  as the solution.

 

 

lbendlin
Super User
Super User

You only have a single table. Your slicers will overlay (ie both will be applied together) when they filter your data.

 

Use disconnected tables for your slicer and then use measures as visual filters for your tables A and B.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors