Latest Enterprise DNA Initiatives

Selection condition 1 or more

How to write condition in dax: If you selected 1 or more categories from slicer, show true. If you did not select anything then show false

@Harris,

Give this a go:

Multiple Select = 

VAR AllRows =
CALCULATE(
    COUNTROWS( Regions ),
    REMOVEFILTERS( Regions )
)

VAR SelRows =
COUNTROWS( VALUES( Regions ))

RETURN
IF( AllRows <> SelRows, TRUE, FALSE )

I hope this is helpful. Full solution file posted below.

  • Brian

eDNA Forum - Check for Multiple Slicer Selections Solution.pbix (357.3 KB)

1 Like

Your solution actually works, but I didn’t foresee one:
If I have a filtered country on the level of the entire page and I do not have a selected country on the filter, the measure shows true and should be false. Can you fix it?
(Prawda = True :))

@Harris,

OK, this was a fascinating curveball… I think I’ve got a working solution, but it gets complicated in a hurry. Bottom line is I don’t think with a standard slicer connected within your data model it is possible to distinguish between filters coming from the slicer vs. the filter pane, but this definitely IS possible with a disconnected table that you can make work like a slicer. For purposes of illustration, the standard slicer will be in black, the disconnected slicer in red. Disconnected slicer was created via New Table using VALUES( Region[Country] ).

To get the disconnected table working like a standard slicer, you first have to create a virtual relationship on your measures using TREATAS. For this example, the revised Sales measure looks like this:

Sales TREATAS = 
CALCULATE(
    [Sales],
    TREATAS(
        VALUES( 'Disconn Country'[Country] ),
        Regions[Country]
    )
) 

and the results are identical:

The other thing you need to do is carry the filter from the page filter pane to the disconnected slicer visual filter using the IN operator:

Disconn in Filt Connected = 
IF( SELECTEDVALUE( 'Disconn Country'[Country] ) IN VALUES( Regions[Country] ), 1, 0)

and set the value in the slicer visual pane to 1 (i.e., only showing those disconnected slicer items that are selected in the page filter pane). So far, so good.

Now we need two measures that will return the same value when no selection is made in the disconnected slicer (regardless of whether the page filter pane is active or not), but different values when a selection(s) is made in the disconnected slicer. These two measures fit the bill:

Test Values Disconn =
COUNTROWS( VALUES( ‘Disconn Country’[Country] ))

and

Test ALLSELECTED REMOVEFILTERS = 

CALCULATE(
    COUNTROWS( ALLSELECTED( Regions[Country] )),
    REMOVEFILTERS(Regions[Country] )
)

Now, here’s the final measure that returns your desired result:

Ultimate Test =

IF(
    [Test ALLSELECTED REMOVEFILTERS] = [Test Values Disconn], FALSE,
    TRUE()
)

Here’s what the two scenarios look like in action:

Scenario 1: No selection made in disconnected “slicer”, page filter pane active

Scenario 2: Selection(s) made in disconnected “slicer”, page filter pane active

I hope this gets you what you need. Fascinating problem - I really enjoyed this one.

Full solution file attached below.

3 Likes