AND Slicer

[Anonymous]

Hi all,

I have an AND slicer that I have picked up via various other posts on here, but wondered if it could be modified slightly. The DAX is:

AND Slice =

VAR _EmptySlicer =

NOT CALCULATE ( ISFILTERED ( Sheet1[Sub] ), ALLSELECTED ( Sheet1[Sub] ) )

RETURN

IF (

NOT ( _EmptySlicer ),

IF (

CALCULATE ( DISTINCTCOUNT ( Sheet1[Sub] ), ALLSELECTED ( Sheet1[Sub] ))

= COUNTROWS ( ALLSELECTED (Sheet1[Sub] ) ),

1

))

However, what I want to do is have the AND filter apply to the 'sub' column, but also to ANY entries on the 'Head' column.

So for example, Head is UK, and then that breaks down to Manchester, Birmingham, etc.  

I would want the filter to either display all results when UK was ticked, or apply the AND approach for specific 'sub' entries. 

Can this be done purely via DAX, or should I look to restructure the tables?  

Thanks!

[v-yangliu-msft]

Hi  @Anonymous ,

I create some data:

Here are the steps you can follow：

1. Create calculated table.

Slice = SUMMARIZE('Table','Table'[Sub])

2. Create measure.

Flag =
var _selected=SELECTEDVALUE(Slice[Sub])
var _first=CALCULATE(MIN('Table'[Hear]),FILTER(ALL('Table'),[Sub]=_selected))
return
IF(MAX('Table'[Hear]) = _first,1,0)

3. Place Measure[Flag] in Filters, set is = 1, and Apply filter.

4. Use [Sub] of the Slice table as the slicer

5. Result:

Select B, it will display all the information of B1 in [Hear] corresponding to [Country]

If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Okay, I think the simplest way to look at this is my formula works too well at filtering things out.

AND Slice =

VAR _EmptySlicer =

NOT CALCULATE ( ISFILTERED ( Sheet1[Sub] ), ALLSELECTED ( Sheet1[Sub] ) )

RETURN

IF (

NOT ( _EmptySlicer ),

IF (

CALCULATE ( DISTINCTCOUNT ( Sheet1[Sub] ), ALLSELECTED ( Sheet1[Sub] ))

= COUNTROWS ( ALLSELECTED (Sheet1[Sub] ) ),

1

))

So, unless there is a [sub] selected then nothing is returned at all - which is GREAT! 

But... 

I would also like to have a result returned when a [head] is selected, I thought I could use something like an OR command (so IF [sub] OR [head], etc) that would cover it, but I think it's a level of DAX understanding that I don't quite have yet.

It may be a case of having it look at the [head] first, and then the [sub] and the issue is that I had originally built this based on the sub and then expanded from there (with various demands) so it could require a more through re-write. 

It would also be good to make it check other fields for inputs (I have a [note] field that it would be great for it to run a check on too) but I need to make sure I am keeping things within my limited knowledge! 

So, not sure if it is possible, but will keep on thinking.

[Anonymous · Accepted solution]

Duh. 

First of all, I had named one of my measures wrong - so it wasn't working. 

Once I had fixed that, I then created a duplicate measure that addressed the [Head] skills, and then a third measure that compared the two and gave an outcome based on that evaluation.

There's probably a more graceful way to do it, but this way worked. 

[Anonymous]

Sorry - this doesn't do quite what I want (it produces results quite similar to the one I already have) but I think the issue is not in your response, but in my question and what I am trying to make my sheet do! 

Will have a re-think about how to be clearer. 

[Anonymous]

Sorry to add a bit more detail on how this measure works.

Basically, I apply the 'AND slicer' as a filter in the PowerBI, with 'AND slice' is not blank being a criteria that has to be met when displaying results (obviously if I remove this then it reverts to behaving like an OR filter). 

Is there another way that people do AND slices, that might allow to retain this functionality, and also expand it to consider entries in other fields (and therefore not require a [sub] entry).