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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jimneal
New Member

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

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")))

2 REPLIES 2
gpoggi
Responsive Resident
Responsive Resident

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:

Users.png

 

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

Users_Selection.png

 

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:

 

Users_TableFilter.png


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

Users_ChicletSlicer.png

 

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

Users_Single.png

 

Users_Multiple.png

 

Users_All.png

 

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors