selecting multiple values in slicer

[monikamishra]

I have this graph which changes with the selection of slicer (single select)

Graph

The slicer logic is :

Calculated Internal = SWITCH(true,

        min(R_MetricGroups[Metric Name])="0 to 4 weeks",FactORBacklogDaily[OR Backlog Cases 0 to 4 Weeks (Internal)],

        min(R_MetricGroups[Metric Name])="5 to 12 weeks",FactORBacklogDaily[OR Backlog Cases 5 to 12 Weeks (Internal)],

        min(R_MetricGroups[Metric Name])="13 to 24 weeks",FactORBacklogDaily[OR Backlog Cases 12 to 24 Weeks (Internal)],

min(R_MetricGroups[Metric Name])="24+ weeks",FactORBacklogDaily[OR Backlog Cases 24 Plus Weeks (Internal)],

        min(R_MetricGroups[Metric Name])="Total",FactORBacklogDaily[OR Backlog Cases Total (Internal)])

Now the requirement is to select multiple buttons and the graph will show the sum. How to achieve that ?

[amitchandak · Accepted solution]

@monikamishra ,Try a measure like. You can have a measure like this. I have used different logic for 5 , assuming that is total , you can change as per need 

measure =
var _1 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="0 to 4 weeks"), R_MetricGroups[Metric Name])+0
var _2 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="5 to 12 weeks"), R_MetricGroups[Metric Name])+0
var _3 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="13 to 24 weeks"), R_MetricGroups[Metric Name])+0
var _4 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="24+ weeks"), R_MetricGroups[Metric Name])+0
var _5 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="Total"), R_MetricGroups[Metric Name])+0

return
if(_5>0, FactORBacklogDaily[OR Backlog Cases Total (Internal)]

if(_1 >0 , FactORBacklogDaily[OR Backlog Cases 0 to 4 Weeks (Internal)], 0) +
if(_2 >0 , FactORBacklogDaily[OR Backlog Cases 5 to 12 Weeks (Internal)], 0) +
if(_3 >0 , FactORBacklogDaily[OR Backlog Cases 12 to 24 Weeks (Internal)], 0) +
if(_4 >0 , FactORBacklogDaily[OR Backlog Cases 24 Plus Weeks (Internal)], 0)
)

[monikamishra]

Hi @v-janeyg-msft ,

Thanks for your response! You are correct. WIhen I select ‘total’,other options are invalid and doesn't work. So logic wise, that's correct. But I wanted to show other buttons as deselected.

Anyway, thanks for your help. 🙂

[monikamishra]

Thank you guys - @amitchandak , @v-janeyg-msft  !! Both the solutions work. You guys are awesome!

Another question - As per client's requirement, one can multi select slicer options which displays the summation. For this I have built the logic as provided by you. Now the client is asking, if the "Total" button is selected, all the other buttons gets deselected. Is it even feasible ? If yes, how ?

[v-janeyg-msft]

Hi, @monikamishra 

It seems that PBI does not support this feature.But in my formula,when you select ‘total’,other options are invalid and won't work.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[v-janeyg-msft · Accepted solution]

Hi, @monikamishra 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure to calculate the desired result.

Like this: 

Calculated Internal =
VAR a =
    IF (
        "0 to 4 weeks" IN DISTINCT ( R_MetricGroups[Metric Name] ),
        FactORBacklogDaily[OR Backlog Cases 0 to 4 Weeks (Internal)],
        0
    )
VAR b =
    IF (
        "5 to 12 weeks" IN DISTINCT ( R_MetricGroups[Metric Name] ),
        FactORBacklogDaily[OR Backlog Cases 5 to 12 Weeks (Internal)],
        0
    )
VAR c =
    IF (
        "13 to 24 weeks" IN DISTINCT ( R_MetricGroups[Metric Name] ),
        FactORBacklogDaily[OR Backlog Cases 13 to 24 Weeks (Internal)],
        0
    )
VAR d =
    IF (
        "24+ weeks" IN DISTINCT ( R_MetricGroups[Metric Name] ),
        FactORBacklogDaily[OR Backlog Cases 24 Plus Weeks (Internal)],
        0
    )
RETURN
    IF (
        "Total" IN DISTINCT ( R_MetricGroups[Metric Name] ),
        FactORBacklogDaily[OR Backlog Cases Total (Internal)],
        a + b + c + d
    )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak · Accepted solution]

@monikamishra ,Try a measure like. You can have a measure like this. I have used different logic for 5 , assuming that is total , you can change as per need 

measure =
var _1 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="0 to 4 weeks"), R_MetricGroups[Metric Name])+0
var _2 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="5 to 12 weeks"), R_MetricGroups[Metric Name])+0
var _3 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="13 to 24 weeks"), R_MetricGroups[Metric Name])+0
var _4 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="24+ weeks"), R_MetricGroups[Metric Name])+0
var _5 = countx(filter(R_MetricGroups, R_MetricGroups[Metric Name]="Total"), R_MetricGroups[Metric Name])+0

return
if(_5>0, FactORBacklogDaily[OR Backlog Cases Total (Internal)]

if(_1 >0 , FactORBacklogDaily[OR Backlog Cases 0 to 4 Weeks (Internal)], 0) +
if(_2 >0 , FactORBacklogDaily[OR Backlog Cases 5 to 12 Weeks (Internal)], 0) +
if(_3 >0 , FactORBacklogDaily[OR Backlog Cases 12 to 24 Weeks (Internal)], 0) +
if(_4 >0 , FactORBacklogDaily[OR Backlog Cases 24 Plus Weeks (Internal)], 0)
)