Slicer with if conditions

[marcelmuench]

Hi community,

first of all: Happy new year!

We have a sales dashboard, with some slicers in it. For one of the slicers we've created a custom column which is based on if conditions.

Customer Cluster =
switch(true(),
if('Sales'[Channel] = "1",TRUE(),FALSE()),"Direct",
if('Sales'[Customer]="5",TRUE(),FALSE()),"Customer XY",
if('Sales'[Category]= "6",TRUE(),FALSE()),"TVs",
"All Others")

This one is working good, if I choose "Direct" in the slicers. If I choose "Customer XY", then all of the sales data with Channel = 1 are missing. Same if I choose "TVs". I know, this is due to the if logic. But this leads to my question. Is there any possibility to have the whole figures for each value I choose in the slicer, and not just the "rest of it".

I hope you're able to help me out with this.

Thanks a lot.

Best regards

[AlB · Accepted solution]

@marcelmuench 

Here is a possible solution. You might have to adapt it, since you still haven't explained the requirements in detail.

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@marcelmuench 

Ok. Like i said,  you need to specify exactly how you need this to work for all cases. Otherwise we'll be running in circles.  From what I see having a column for the slicer is not the best option here since that implies each row in your table can belong only to one cluster.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[marcelmuench]

What are the other possibilities besides a column? I think it's not possible to have a measure as a slicer. What would you recommend to do in this case?

[AlB]

@marcelmuench 

Of course. You need to specify clearly how the clusters are assigned. What happens exactly when more than one of the conditions is met, like in both the rows you are showing? This should fix the issue at hand, but might break in other cases without futher clarification:

Customer Cluster =
SWITCH (
    TRUE (),
    'Sales'[Customer] = "5", "Customer XY",
    'Sales'[Channel] = "1", "Direct",
    'Sales'[Category] = "6", "TVs",
    "All Others"
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[marcelmuench]

Thanks! But at the end TV is 0, instead of 300. How is it possible to specify the clusters? Don't know how to do that ☹️

[marcelmuench]

Thanks for your reply!

For example:

Channel	Customer	Category	Value
1	5	6	100
2	5	6	200

Now I set the slicer to "Direct" and the result is 100 - which is correct.

If I select "Customer XY", then I get 200, but i expect 300 as a result.

I hope you understood what I want to do now.

[AlB]

Hi @marcelmuench 

Please explain this better:

Is there any possibility to have the whole figures for each value I choose in the slicer, and not just the "rest of it".

I don't understand what you want to do. Show an example based on sample data, oncludign the expected result

In any case, you do not need the IFs. To start with, you can simplify your currrent code:

Customer Cluster =
SWITCH (
    TRUE (),
    'Sales'[Channel] = "1", "Direct",
    'Sales'[Customer] = "5", "Customer XY",
    'Sales'[Category] = "6", "TVs",
    "All Others"
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers