Using IF(AND) to display three choices for a slicer

[jimneal]

I am attempting to create a chiclet slicer for the number of computers displaying in billing (ultimately to identify users who have multiple computers). 

I can do the following at it works but both "Single" and "Multiple" have to be selected for all the computers to display.

Mult_Comp = IF(EEC_Computers[MultipleComputers] = Blank, "Single", IF(EEC_Computers[MultipleComputers] > 1, "Multiple"))

Therefore I wanted to add an All that counted all the Blank (signifying the user is only being billed for one computer) AND those having more than one computer. I have researched and found where you can use IF(AND) but cannot seem to get it to work. This is what I have

test1 = IF(AND(EEC_Computers[MultipleComputers] = Blank, EEC_Computers[MultipleComputers] > 0), "All",
IF(EEC_Computers[MultipleComputers] = Blank, "Single", IF(EEC_Computers[MultipleComputers] > 1, "Multiple")))

[gpoggi]

Hi  @jimneal ,

I think I know what you are trying to accomplish with the slicer but what I don't get very clear is your condition... when a user has multiple or single computers. But in order to help you let's assume the following:

This is your condition:

if ( [MultipleComputers] = BLANK() or [MultipleComputers] = 1 ) then "Single" else "Multiple"

This is your EEC_Computers table:

So, to start, create a new table with the values you need to be displayed in Chiclet Slicer:

Then, go to you EEC_Computers table and create a custom column for field [Mult_Comp] (which was created following the above condition, if it is different you can change it accordingly).
In this case I used 1 for "Mutiple" and 0 for "Single"

Mult_Comp = IF(EEC_Computers[MultipleComputers]>1,1,0)

Now, create this measure which is going to indicate the behavior according the option selected from chiclet slicer:

ComputersSelected = 
SWITCH (True(),
VALUES('Chiclet Slicer Values'[Selection]) = "All", "Show",
VALUES('Chiclet Slicer Values'[Selection]) = "Multiple" && SUM(EEC_Computers[Mult_Comp]) = 1, "Show",
VALUES('Chiclet Slicer Values'[Selection]) = "Single" && SUM(EEC_Computers[Mult_Comp]) = 0, "Show",
ISBLANK(SELECTEDVALUE('Chiclet Slicer Values'[Selection])),"Show",
"No")

Add a table visual and add the fields you need to display (in my case [User] and [MultipleComputers].

Select this Table visual and go to Filters pane and add the following filter:

Finally add the Chiclet Slicer visual and use the [Selection] field as Values:

And that's it. Now you will get your Table visual filtered according the option selected:

Hope this helps, if you have any question, just let me know, if not I'll appreciate if you mark this answer as Solution :).

Regards,

Gian Carlo Poggi

[JosefPrakljacic]

Hey @jimneal ,

may I ask you to provide us with some sample data and the expected result.

I'm having a hard time understanding what you actually want.

BR,

Josef