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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pastor84
Regular Visitor

Why this switch does not work?

Hi everyone,

 

I have included a SWITCH function in a slider to alternate a visualization between a measure "A", a measure "B" and the sum of both if nothing is selected in the slider. The functions is as follows:

 

BF_Slicer =
IF(ISCROSSFILTERED(Balances[Concepto]),
SWITCH(true(),
VALUES(Balances[Concepto]) = "Curr. Acc.", [BF_CA],
VALUES(Balances[Concepto]) = "Term dep.", [BF_TD],
0), [BF_CA]+[BF_TD])
 
If I put this measure in a Tachometer visualization it works. However, If I put it in a Circle graph, it does not work when nothing is selected. (If there is something selected in the slider, there is no problem). The error I get  says it was expecting one single value and I provided several values.
 
Any idea what is wrong?
1 REPLY 1
AmiraBedh
Resident Rockstar
Resident Rockstar

From the description of your problem, it seems the issue arises from the multi-value nature of the `VALUES` function when there's no selection in the slicer. This is especially true for visuals that expect single values.


1. When a single item is selected from the slicer:
- `ISCROSSFILTERED` returns TRUE and we enter the `SWITCH` function.
- `VALUES` will return a table with a single value, hence the comparisons you're making with `VALUES(Balances[Concepto])` are valid.

2. When no item is selected in the slicer:
- `ISCROSSFILTERED` returns FALSE.
- The formula goes to the else-part, which is `[BF_CA]+[BF_TD]`, and there shouldn’t be a problem here as it’s just a sum of two measures.

3. When multiple items are selected:
- This is probably where the problem arises. `VALUES` returns a table of multiple values, which isn’t valid for direct comparisons like `VALUES(Balances[Concepto]) = "Curr. Acc."`. If the visual is expecting a single value, it'll throw an error.


BF_Slicer =
VAR SelectedValueCount = COUNTROWS(VALUES(Balances[Concepto]))
RETURN
IF(
SelectedValueCount = 0, [BF_CA] + [BF_TD],
IF(
SelectedValueCount = 1,
SWITCH(TRUE(),
VALUES(Balances[Concepto]) = "Curr. Acc.", [BF_CA],
VALUES(Balances[Concepto]) = "Term dep.", [BF_TD],
BLANK()
),
BLANK() -- This line handles multiple selections, you can modify this to return a specific value or just blank
)
)


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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