Filter slicer with "AND" operator using other slicers

[Anonymous]

I have 4 slicers to filter data :

• Period
• Brand
• Sample
• Country

I want to filter the countrie's selection list with the combination of the previous 3 filters with an "AND" operator and not an "OR" (like power bi default filtering) . Here's an example :

The slicers Period and Brand are a single value selection.

The slicer Sample is a multivalue selection.

For example I choose the value P1 for Period's slicer, B1 for brand's slicer and two values for Sample's slicer : S1 and S2

Here's a data sample :

Period	Brand	Sample	Country
P1	B1	S1	Australia
P1	B1	S2	Australia
P1	B1	S1	Spain
P1	B1	S1	France
P1	B1	S1	Italy
P1	B1	S2	Portugal
P1	B1	S1	Germany
P1	B1	S2	Germany
P1	B1	S4	Greece

I've activated cross filtering between my slicers so the country slicer's list shows : Australia - Spain - France - Italy - Portugal - Germany 

but it should list only two countries which are : Australia and Germany

How can I achieve this ?

I've created a measure that do this process by concatenating countries but I didn't find a way to filter my slicer's selection with the result of my measure :

countries list = 
VAR AllSample =
    COUNTROWS ( VALUES (  Table1[Sample] ))
RETURN
    CALCULATE (
        CONCATENATEX(Table2,Table2[Country],"/"),
        FILTER (
            VALUES ( Table2[Country] ),
            CALCULATE ( COUNTROWS ( VALUES ( Table1[Sample] ) ) ) = AllSample
        )
    )

I'm open to all suggesstions.

Thanks !

[smpa01]

@Anonymous  you can write the following measure

Measure =
VAR _slicerCount =
    COUNTX ( ALLSELECTED ( 'Table'[Sample] ), 'Table'[Sample] )
VAR _countryCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Sample] ),
        ALLEXCEPT ( 'Table', 'Table'[Country] )
    )
VAR _maxCountry =
    CALCULATE ( MAX ( 'Table'[Country] ) )
RETURN
    IF ( _slicerCount = _countryCount, _maxCountry )

then you can do this

[smpa01]

@Anonymous  did you  try the above?

[Anonymous]

Hi @smpa01 

I'm trying to adapt your solution to my real use case but unfortunately I'm not having the desired output as the sample used above.

I understood both of the variables used in the measure "country count" and "slicer count" (correct me if I'm wrong)

• country count : counts the distinct samples within countries
• slicer count : counts the number of sample slicer's selection

In my case, the "slicer count" works perfectly but the "country count" shows the count of all different samples within a country (and not just the count of the selected samples in the slicer)

Here's an example :

I've selected 3 samples in my case, so the "slicercount" is correct, otherwise in the country count column, you can see that I have 10 and 8 which refers to the distinct count of all samples within the specified country. Or the values should be : 2 when it's 8 and 3 when it's 10.

So the output condition is never true ( because 3 <> (8 nor 10 )) and that's why I'm having always a blank table.

I don't want to be rude, but do you have a solution for this ?

Feel free to ask me questions.

Thanks in advance.

[smpa01]

@Anonymous can you please provide the sample again?

[Anonymous]

Sorry for the delay, here's the link of the sample

https://drive.google.com/file/d/1gB6AnAAGsblLxq8j6V6fuom_63ktQBjK/view?usp=sharing 

[smpa01]

@Anonymous I have tested out the measure and it is working as desired (following the logic discusssed above)

Measure2 =
VAR _slicerCount =
    COUNTX ( ALLSELECTED ( t3[SampleRespondent] ), t3[SampleRespondent] )
VAR _countryCount =
    CALCULATE (
        DISTINCTCOUNT ( t3[SampleRespondent] ),
        ALLEXCEPT ( t3, t3[ID_Country] )
    )
VAR _maxCountry =
    CALCULATE ( MAX ( t3[ID_Country] ) )
RETURN
    IF ( _slicerCount = _countryCount, _maxCountry )

I have analyzed the data and these are the stat of the data

| ID_Country | Count | Combination |
|------------|-------|-------------|
| AE         | 2     | G3,G7       |
| AR         | 2     | G3,G7       |
| AU         | 3     | G15,G3,G7   |
| BE         | 2     | G3,G7       |
| BR         | 2     | G3,G7       |
| CA         | 3     | G15,G3,G7   |
| CL         | 2     | G3,G7       |
| CN         | 3     | G15,G3,G7   |
| CO         | 2     | G3,G7       |
| CZ         | 2     | G3,G7       |
| DE         | 3     | G15,G3,G7   |
| DK         | 2     | G3,G7       |
| ES         | 3     | G15,G3,G7   |
| FR         | 3     | G15,G3,G7   |
| GB         | 3     | G15,G3,G7   |
| GR         | 2     | G3,G7       |
| HK         | 3     | G15,G3,G7   |
| HU         | 2     | G3,G7       |
| ID         | 3     | G15,G3,G7   |
| IN         | 3     | G15,G3,G7   |
| IT         | 3     | G15,G3,G7   |
| JP         | 3     | G15,G3,G7   |
| KR         | 3     | G15,G3,G7   |
| MX         | 3     | G15,G3,G7   |
| MY         | 3     | G15,G3,G7   |
| NL         | 2     | G3,G7       |
| NZ         | 3     | G15,G3,G7   |
| PH         | 3     | G15,G3,G7   |
| PL         | 2     | G3,G7       |
| RO         | 2     | G3,G7       |
| RU         | 3     | G15,G3,G7   |
| SA         | 2     | G3,G7       |
| SE         | 2     | G3,G7       |
| SG         | 3     | G15,G3,G7   |
| TH         | 3     | G15,G3,G7   |
| TR         | 2     | G3,G7       |
| TW         | 3     | G15,G3,G7   |
| UA         | 2     | G3,G7       |
| US         | 3     | G15,G3,G7   |
| VN         | 3     | G15,G3,G7   |
| ZA         | 2     | G3,G7       |

So if you make a selection of G3+G7 like following, the measure should give you 36 rows, which it is doing

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Tom & Jerry"}, 't3'[Brand])

  VAR __DS0FilterTable2 = 
    TREATAS({"P1"}, 'Table'[Period])

  VAR __DS0FilterTable3 = 
    TREATAS({"G3",
      "G7"}, 't3'[SampleRespondent])

  VAR __ValueFilterDM0 = 
    FILTER(
      KEEPFILTERS(
        SUMMARIZECOLUMNS(
          't3'[Brand],
          't3'[ID_Country],
          't3'[Period],
          't3'[SampleRespondent],
          __DS0FilterTable,
          __DS0FilterTable2,
          __DS0FilterTable3,
          "Measure_2", IGNORE('t3'[Measure 2])
        )
      ),
      NOT(ISBLANK([Measure_2]))
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      't3'[Brand],
      't3'[ID_Country],
      't3'[Period],
      't3'[SampleRespondent],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __ValueFilterDM0,
      "Measure_2", 't3'[Measure 2]
    )

  VAR __DS0PrimaryShowAll = 
    ADDMISSINGITEMS(
      't3'[Brand],
      't3'[ID_Country],
      't3'[Period],
      't3'[SampleRespondent],
      __DS0Core,
      't3'[Brand],
      't3'[ID_Country],
      't3'[Period],
      't3'[SampleRespondent],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __ValueFilterDM0
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      501,
      __DS0PrimaryShowAll,
      't3'[Brand],
      1,
      't3'[ID_Country],
      1,
      't3'[Period],
      1,
      't3'[SampleRespondent],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  't3'[Brand], 't3'[ID_Country], 't3'[Period], 't3'[SampleRespondent]

if you make a selection of G3+G7+G15 like following, the measure should give you 69 rows, which it is doing

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Tom & Jerry"}, 't3'[Brand])

  VAR __DS0FilterTable2 = 
    TREATAS({"P1"}, 'Table'[Period])

  VAR __DS0FilterTable3 = 
    TREATAS({"G3",
      "G7",
      "G15"}, 't3'[SampleRespondent])

  VAR __ValueFilterDM0 = 
    FILTER(
      KEEPFILTERS(
        SUMMARIZECOLUMNS(
          't3'[Brand],
          't3'[ID_Country],
          't3'[Period],
          't3'[SampleRespondent],
          __DS0FilterTable,
          __DS0FilterTable2,
          __DS0FilterTable3,
          "Measure_2", IGNORE('t3'[Measure 2])
        )
      ),
      NOT(ISBLANK([Measure_2]))
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      't3'[Brand],
      't3'[ID_Country],
      't3'[Period],
      't3'[SampleRespondent],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __ValueFilterDM0,
      "Measure_2", 't3'[Measure 2]
    )

  VAR __DS0PrimaryShowAll = 
    ADDMISSINGITEMS(
      't3'[Brand],
      't3'[ID_Country],
      't3'[Period],
      't3'[SampleRespondent],
      __DS0Core,
      't3'[Brand],
      't3'[ID_Country],
      't3'[Period],
      't3'[SampleRespondent],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __ValueFilterDM0
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      501,
      __DS0PrimaryShowAll,
      't3'[Brand],
      1,
      't3'[ID_Country],
      1,
      't3'[Period],
      1,
      't3'[SampleRespondent],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  't3'[Brand], 't3'[ID_Country], 't3'[Period], 't3'[SampleRespondent]

pbix is attached

[Anonymous]

@smpa01 I figured out that I provided you the wrong sample I'm really sorry.

Please download this data sample instead :

https://drive.google.com/file/d/1Mjnvef1CnBdsmHZWIsJicwbcL6lBnZJT/view?usp=sharing 

As you can see the IF condition is never true in this case (It seems like the country count in this sample calculates the number of samples within countries without taking into consideration the selection in the segment, in this case B3 + B7)

Thanks

[smpa01 · Accepted solution]

@Anonymous 

EDIT

can you please try the attached pbix tab called Page3 (with slicer table) and Page4 (from same table)

//with slicer table
Measure4 =
VAR _select =
    ALLSELECTED ( slicer_respondent[Respondent] )
VAR _slicerCount =
    COUNTX ( _select, slicer_respondent[Respondent] )
VAR _countryCount =
    CALCULATE (
        DISTINCTCOUNT ( t4[SampleRespondent] ),
        t4[SampleRespondent] IN _select
    )
VAR _maxCountry =
    CALCULATE ( MAX ( t4[ID_Country] ) )
VAR _result =
    IF ( _slicerCount = _countryCount, _maxCountry )
RETURN
    _result

//slicer from same fact table
Measure5 =
VAR _select =
    ALLSELECTED ( t4[SampleRespondent] )
VAR _slicerCount =
    COUNTROWS ( _select )
VAR _countryCount =
    CALCULATE (
        DISTINCTCOUNT ( t4[SampleRespondent] ),
        t4[SampleRespondent] IN _select
    )
VAR _maxCountry =
    CALCULATE ( MAX ( t4[ID_Country] ) )
VAR _result =
    IF ( _slicerCount = _countryCount, _maxCountry )
RETURN
    _result

[Anonymous]

@smpa01 thank you for your time. It works

[smpa01]

@Anonymous  with 

P1-B1-(S1+S2) selection what i sthe logic that ot should only return Australia and Germany and not below

[Anonymous]

Hi @smpa01 , to make it simple we will focus only on Sample and Country slicers :

in the example above : I've selected S1 and S2 for the Sample values, So the country slicer should show only Australia and Germany because these two countries are related to these two samples (S1 and S2) 

Which is not the case of portugal (just S2) neither Italy (S1) ...