The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Ignore all slicers except for one

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Ignore all slicers except for one

08-18-2023
07:48 PM

Hi,

I am have a report where i'd like to ignore all slicer selections except for CY[Slicer]. I am using below code however it returns attached error message. Is there a work around this issue?

```
test = DIVIDE(
CALCULATE(
(Output[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Gross Profit"} ),
CALCULATE(
('Output'[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Net Sales"} ),
FILTER(
ALL('CY'[Slicer]),
'CY'[Slicer] = SELECTEDVALUE('CY'[Slicer])
))
```

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-19-2023
07:58 PM

@danextian Thanks for your response rather than using allexcept removefilters/values did the trick.

```
Output Fixed GP Rate =
VAR _GP =
CALCULATE (
('Output'[Output TY]),
REMOVEFILTERS ( 'Output' ),
ValueS('CY'[SLICER]),
'Output'[P&L] = "Gross Profit"
)
VAR _NS =
CALCULATE (
('Output'[Output TY]),
REMOVEFILTERS ( 'Output' ),
ValueS('CY'[SLICER]),
'Output'[P&L] = "Net Sales"
)
RETURN
IF (
ISBLANK ( DIVIDE ( _GP, _NS ) )
|| DIVIDE ( _GP, _NS ) = 0,
SELECTEDVALUE ( 'CY'[Slicer] ),
DIVIDE ( _GP, _NS )
)
```

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-18-2023
08:04 PM

Hi @dokat ,

The second argument in your DIVIDE formula does not return a numeric value but a table instead:

```
FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )
```

By the looks of it you're simply trying to access the value that is currently selected from the 'CY'[Slicer] column. If that is the case, SELECTEDVALUE ( 'CY'[Slicer] ) should be enough.

Also, why use IN when there is only one value within a list? This only has one value in it

`{"Gross Profit"}`

Could have been simply written as

`'Output'[P&L] = "Gross Profit"`

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

Need Power BI consultation, get in touch with me on

Learn with me on

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-18-2023
08:17 PM

@danextian Thank you for your response, I modified the code per your recommendation. However i an still getting the same error message. in CY[Slicer] it has ty vs last year last month date selection.

```
test = DIVIDE(
CALCULATE(
(Output[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] = "Gross Profit" ),
CALCULATE(
('Output'[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Net Sales"} ),
SELECTEDVALUE('CY'[Slicer]))
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-18-2023
08:33 PM

Hi @dokat ,

Let me correct my previous reply. I was thinking you were using a table as the second argument in divide but didnt realize it was a third argument. Either way, that would still return an error.

As per documentation, the alternate result when the division returns 0 must be a constant. The below formula will return the word error as the alternate result is a constant:

`Test = DIVIDE(1,0, "error")`

However, this will return an error as the alternate result is not a constant.

`Test = DIVIDE(1,0, max(1,0))`

You can use IF instead of using the alternate result argument which would be something like

```
=
IF (
[divide measure] = 0
|| ISBLANK ( [divide measure] ),
SELECTEDVALUE ( table[column] ),
[divide measure]
)
```

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

Need Power BI consultation, get in touch with me on

Learn with me on

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-18-2023
08:52 PM

@danextian I am still cominga cross same issue and modified the code as below, Formula shouldnt be returning Test = DIVIDE(1,0, "error")...if there is value in numerator denominator should have have a value too. I think part of the issue is CY[Slicer} is in differet table. It is not in 'Output' table

```
test = IF(DIVIDE(
CALCULATE(
(Output[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Gross Profit"} ),
CALCULATE(
('Output'[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Net Sales"} ),
FILTER(
ALL('CY'[Slicer]),
'CY'[Slicer] = SELECTEDVALUE('CY'[Slicer])
))=0
|| ISBLANK(DIVIDE(
CALCULATE(
(Output[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Gross Profit"} ),
CALCULATE(
('Output'[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Net Sales"} ),
FILTER(
ALL('CY'[Slicer]),
'CY'[Slicer] = SELECTEDVALUE('CY'[Slicer])
))),
SELECTEDVALUE('CY'[Slicer]),
DIVIDE(
CALCULATE(
(Output[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Gross Profit"} ),
CALCULATE(
('Output'[Output TY]),
ALLSELECTED('Output'),
'Output'[P&L] in {"Net Sales"} ),
FILTER(
ALL('CY'[Slicer]),
'CY'[Slicer] = SELECTEDVALUE('CY'[Slicer])
)))
```

I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-19-2023
04:03 AM

As previously mentioned, this will return a table:

`FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )`

If you put this in a separate measure, you'll get something like:

*The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. *This alone will cause an error. SELECTEDVALUE ( 'CY'[Slicer] ) should have sufficed but even so, this will cause an error. As mentioned, the third argument in DIVIDE must be a constant. If you dissect the DIVIDE within your IF conditions, the lines in below below are still not constants - they're variables that are dependent on the value of the current filter contexts or other calculations - they should be a plain number or a text string.

=

IF (

DIVIDE (

CALCULATE (

( Output[Output TY] ),

ALLSELECTED ( 'Output' ),

'Output'[P&L] IN { "Gross Profit" }

),

CALCULATE (

( 'Output'[Output TY] ),

ALLSELECTED ( 'Output' ),

'Output'[P&L] IN { "Net Sales" }

),**FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )**

) = 0

|| ISBLANK (

DIVIDE (

CALCULATE (

( Output[Output TY] ),

ALLSELECTED ( 'Output' ),

'Output'[P&L] IN { "Gross Profit" }

),

CALCULATE (

( 'Output'[Output TY] ),

ALLSELECTED ( 'Output' ),

'Output'[P&L] IN { "Net Sales" }

),**FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )**

)

),

SELECTEDVALUE ( 'CY'[Slicer] ),

DIVIDE (

CALCULATE (

( Output[Output TY] ),

ALLSELECTED ( 'Output' ),

'Output'[P&L] IN { "Gross Profit" }

),

CALCULATE (

( 'Output'[Output TY] ),

ALLSELECTED ( 'Output' ),

'Output'[P&L] IN { "Net Sales" }

),**FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )**

)

)

Instead of writing this very log formula, I would have used variables instead or write those within calculate in a separate measure. I don't also see why use an IN operator when there's only one value in the list after it. { "Gross Profit" } - there is only "Gross Profit" inside the curly brackets, nothing else.

```
=
VAR _GP =
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Gross Profit"
)
VAR _NS =
CALCULATE (
( 'Output'[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Net Sales"
)
RETURN
IF (
ISBLANK ( DIVIDE ( _GP, _NP ) )
|| DIVIDE ( _GP, _NP ) = 0,
SELECTEDVALUE ( 'CY'[Slicer] ),
DIVIDE ( _GP, _NP )
)
```

or separate GROSS PROFIT and NET SALES mesures to be able to reuse them later

```
Gross Profit =
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Gross Profit"
)
```

```
Net Sales =
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Net Sales"
)
```

```
DIVIDE Measure =
VAR _DIV =
DIVIDE ( [Gross Profit], [Net Sales] )
RETURN
IF ( _DIV = 0 || ISBLANK ( _DIV ), SELECTEDVALUE ( 'CY'[Slicer] ), _DIV )
```

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."

Need Power BI consultation, get in touch with me on

Learn with me on

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-19-2023
07:58 PM

@danextian Thanks for your response rather than using allexcept removefilters/values did the trick.

```
Output Fixed GP Rate =
VAR _GP =
CALCULATE (
('Output'[Output TY]),
REMOVEFILTERS ( 'Output' ),
ValueS('CY'[SLICER]),
'Output'[P&L] = "Gross Profit"
)
VAR _NS =
CALCULATE (
('Output'[Output TY]),
REMOVEFILTERS ( 'Output' ),
ValueS('CY'[SLICER]),
'Output'[P&L] = "Net Sales"
)
RETURN
IF (
ISBLANK ( DIVIDE ( _GP, _NS ) )
|| DIVIDE ( _GP, _NS ) = 0,
SELECTEDVALUE ( 'CY'[Slicer] ),
DIVIDE ( _GP, _NS )
)
```

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

122 | |

119 | |

87 | |

51 | |

43 |

Top Kudoed Authors

User | Count |
---|---|

200 | |

109 | |

80 | |

65 | |

64 |