Mandatory Selection in Binary Slicer

[Anonymous]

Hello everyone,
I currently have a report which is fed with a table that contains orders from multiple customers for various years in which the orders may be in 1 of two states, Gross or Net. An order starts its existence in a Gross status and when it is delivered it is changed to Net.

Currently I’m displaying all the distinct customers for each Month on a report, however I need to allow the final user to display either Gross or Net orders, this could be easily attained with a filter/slicer, however there is the issue that if I want to see Gross orders I would also need to see Net orders (it would not make sense to see Gross orders only without the Net given that Net would already have been Gross at some point). So, I need the user to choose either only Net our Net and Gross, never Only Gross.

The only way to force a selection that I’m aware is through the Single Select” field, however this goes against what I need.

On a side note: I already tried a more complex approach that is to create a “dynamic metric” with a SWITCH function that Selects between two “static/normal metrics) for Gross and Net with the filters inside of them, however I’m not able to use these filters because I also need to use a DATESBETWEEN function which messes up these filters.

Any suggestions would be great.
Kind regards
JG

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the folllowing:

• Create a disconnected table with the states (Gross and Net)
• Add the following measure:

Filtering =
IF (
    SELECTEDVALUE ( 'StatesSlicer'[State] ) = "Net",
    COUNTROWS ( FILTER ( 'Table', 'Table'[State] = "Net" ) ),
    1
)

Use the measure has filter of your visualizations all values that aren't blank:

Check PBIX file attach.