Harvesting multiple Slicers [Power BI]

Question

For a scorecard, I need to weigh some categories based on importance. Said weights must be selectable from a filter based on their category (categories might have different weights).

I created a table with the weights with the percentages to multiply against and created the below:

CAT3 Selection

cat3 Selection =
IF (
    SUM ( Query1[CAT3 Error] ) = BLANK (),
    BLANK (),
    IF (
        HASONEVALUE ( 'Weighted Percents'[CAT3 Weight] ),
        VALUES ( 'Weighted Percents'[CAT3 Weight] ),
        0
    )
)

CAT4 Selection

cat4 Selection =
IF (
    SUM ( Query1[CAT4 Error] ) = BLANK (),
    BLANK (),
    IF (
        HASONEVALUE ( 'Weighted Percents'[CAT4 Weight] ),
        VALUES ( 'Weighted Percents'[CAT4 Weight] ),
        0
    )
)

cat3 Weighted Scenario

( [cat3 Selection] * 1 ) * SUM ( Query1[cat3 Error] )

cat4 Weighted Scenario

( [cat4 Selection] * 1 ) * SUM ( Query1[cat4 Error] )

My problem is this: When I select a slicer, it works, but it also automatically selects the same value on the other slicer. If I edit the interactions between the two slicers, I get a value of 0 in the data unless they are the same. How can I fix this?

Does the Weighted Percents table have a row for each CAT3 and CAT4 weight combination? If not, that's your problem. Having two slicers on the same table doesn't work well if you expect them to be independent. — Alexis Olson
Hi, yes, they do. I did try different tables but got the same results. Would having the results on separate tables as well maybe fix this or is it a value() issue? — Btriggered

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-12T15:57:34

This is a rather convoluted way of harvesting slicers. A more standard way to write things would be like

cat3 weighted =
SELECTEDVALUE ( 'Weighted Percents'[CAT3 Weight] ) * SUM ( Query1[cat3 Error] )

cat4 weighted =
SELECTEDVALUE ( 'Weighted Percents'[CAT4 Weight] ) * SUM ( Query1[cat4 Error] )

If this simplified construction still has the same problem, then I would recommend using independent tables for your slicer selections rather than having just the one.

Harvesting multiple Slicers [Power BI]

2 Answers