Slicer - multiple conditions must be met

[tonyororke]

I am using a slicer in my Power BI report which filters data in a table. Is there a way to ensure that the data in the talble is only presented if all selections/ criteria are met i.e. in the example below I would only want to see "Product 6" in the table as it meets all the selections in the slicer, however, my table shows the product is any of the crietia is met. 

Thanks.

[v-diye-msft]

Hi @tonyororke 

Not sure if you've fixed the question, kindly check my solution whether helps:

Pbix attached

[tonyororke]

Hi - thanks for your reply. 

Let me try this on my data. 

By any chance do you know if it woudl be possible to have the table showing all products and features when nothing is selected in the slicer?

Tony

[v-diye-msft]

Hi @tonyororke 

You need to update your power bi desktop. please find the latest version here:

https://powerbi.microsoft.com/en-us/downloads/ 

[tonyororke]

Thanks - i can see your file. This is great, however it doesn't quite meet the use case. 

My data has three vaiables /columns 

Product - Product1, Product 2, Product 3......................

Feature - Feature 1, Feature2, Feature 3......................

Feature Value - either "YES", "NO", or a numeric value. 

This is how my table looks 

What I am trying to achieve is that when certain features are selected in the slicer, only those products which has "YES" or a numeric value are shown e.g. 

Slicer and visual matrix,. Matrix has Features as Rows, Products as columns and feature values as Values. 

 

[mahoneypat]

Please try this expression to get your desired result.  Just add it to your shown table visual; products that don't have all the selected features will be blank (and not shown).  This assumes you have a relationship between your Features and Products tables on the Feature column.

Product Has ALL Selected Features =
VAR __thisproductsfeatures =
    CALCULATETABLE ( VALUES ( Products[Feature] ), ALL ( Products[Feature] ) )
VAR __selectedfeatures =
    ALLSELECTED ( Features[Features] )
RETURN
    IF (
        ISBLANK ( COUNTROWS ( EXCEPT ( __selectedfeatures, __thisproductsfeatures ) ) ),
        "Yes"
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[tonyororke]

That has worked perfectly.. many thanks. 

The only challenge now is that the table now is blank, until items are selected from the slicer. Is there a way to start with the full data set, and filter down according the items selected in the slicer?

[mahoneypat]

Here you go

Product Has ALL Selected Features =
VAR __thisproductsfeatures =
    CALCULATETABLE ( VALUES ( Products[Feature] ), ALL ( Products[Feature] ) )
VAR __selectedfeatures =
    ALLSELECTED ( Features[Features] )
RETURN
    IF (
        ISFILTERED ( Features[Features] ),
        IF (
            ISBLANK ( COUNTROWS ( EXCEPT ( __selectedfeatures, __thisproductsfeatures ) ) ),
            "Yes"
        ),
        "No Features Selected"
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[tonyororke]

Hi  - thanks for your resposne. Still the same issue though. I have created the measure as described and added it to the visual fiter with "contains = YES"

[mahoneypat]

I assumed you would add it to the table and not use in a Filter.  You can just change "No Features Selected" to "Yes" and it should work.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[tonyororke]

sorry, I think I may have misunderstood. 

I added it as visual level filter on the table. 

step 1. create mesaure using your orignial syntax

step 2. add measure as viaual level filter with "contains = YES". 

That removed all of the data from the table, but when i selected features from the slicer it added the products which had the selected features, which is exactly what i wanted. 

The only thing that i thought would be nice to change is that the table would have all data in it as a starting point. 

thanks again  

[mahoneypat]

Please try this (with "No Features Selected" with "Yes".  I think it will make it work with your visual filter.

Product Has ALL Selected Features =
VAR __thisproductsfeatures =
    CALCULATETABLE ( VALUES ( Products[Feature] ), ALL ( Products[Feature] ) )
VAR __selectedfeatures =
    ALLSELECTED ( Features[Features] )
RETURN
    IF (
        ISFILTERED ( Features[Features] ),
        IF (
            ISBLANK ( COUNTROWS ( EXCEPT ( __selectedfeatures, __thisproductsfeatures ) ) ),
            "Yes"
        ),
        "Yes"
    )

. 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[tonyororke]

Hi - first of all, thanks for the help.. 

I'm still having the same issues, and just wanted to check my understanding as I'm relatively new to DAX. 

My table is called "Product-Trial"

I am createing a measure within the the table that contains both the "product" and "fetures", and applying it as a viual level filter to the the table. The filter is "contains "YES".. The DAX i used for the measure is 

Product Has ALL Selected Features =

VAR __thisproductsfeatures =

CALCULATETABLE ( VALUES ( 'Product-trial'[Feature] ), ALL ( 'Product-trial'[Feature] )

VAR __selectedfeatures =

ALLSELECTED ( 'Product-trial'[Feature] )

RETURN

IF (

ISFILTERED ( 'Product-trial'[Feature] ),

IF (

ISBLANK ( COUNTROWS ( EXCEPT ( __selectedfeatures, __thisproductsfeatures ) ) ),

"Yes"

),

"Yes")

When I apply the above, and have no "feature" selected on the slicer, I don's see anything in the table, but as soon as I start to select the "feature" on the slicer, the associated "products" and "features" appear on the table.

[mahoneypat]

I think I see the problem.  The measure(s) I provided assume you have a separate and disconnected table for your list of Features to go into the slicer.

To make that table, on the Modeling tab, click on New Table and use this formula

Features = DISTINCT('Product-trial'[Feature])

Make sure there is no relationship to the new table, and it in the var __selectedfeatures variable and in the ISFILTERED( ).

Regards,

Pat

[tonyororke]

Hi - unfortunatley still the same result. 

I have added a new table using Features = DISTINCT('Product'[Feature]) and I'm now using that as the data for my slicer. 

I haven't added any relationship to the new "Features" table.

I have also added the "product has all" filter to the  Product / Features table as "contains = YES"

Product Has ALL Selected Features =

VAR __thisproductsfeatures =

CALCULATETABLE ( VALUES ( 'Feature'[Feature] ), ALL ( Feature'[Feature] ) )

VAR __selectedfeatures =

ALLSELECTED ( 'Feature'[Feature] )

RETURN

IF (

ISFILTERED ( 'Feature'[Feature] ),

IF (

ISBLANK ( COUNTROWS ( EXCEPT ( __selectedfeatures, __thisproductsfeatures ) ) ),

"Yes"

),

"Yes"

)