Multiple selected slicer value adding up

[Yachti]

Hi,

I'm new to Power BI and I'm trying to use the slicer component.
The slicer should filter the all company by the Microsoft products in my table but when I select multiple values in the slicer it shows every company where there is at least one matching value. My goal was to only show those companies where every value of the slicer matches.

Here's what it looks like with one selected value. The number on top is the count of the matching companies

And here's what it looks like with multiple selected value and as you can see it shows every company that matches to at least one of the slicer filter.

And here are my tables / ERM

The Microsoft products are in field[name]

How can I make that it only shows the company that matches all filters and not only one?

[OwenAuger]

Hi @Yachti 

Glad to have you in the Power BI Community 🙂

As you've observed, making multiple selections on a single Slicer results in a filter corresponding to the union of the items selected, or an OR condition between the selected items.

To get the result you want with an AND condition, where Companies are filtered to those matching every value selected on the slicer, requires a little DAX.

This article on SQLBI illustrates how it can be done:

https://www.sqlbi.com/articles/apply-and-logic-to-multiple-selection-in-dax-slicer/

From your model diagram, this measure (based on the above article) should count the Companies which match all selected products:

Companies with all Products =
VAR NumOfSelectedProducts =
    COUNTROWS ( VALUES ( field[name] ) )
VAR CompaniesAndProducts =
    SUMMARIZE ( company_field, company[id], field[name] )
VAR CompaniesWithNumProducts =
    GROUPBY (
        CompaniesAndProducts,
        company[id],
        "@Products", SUMX ( CURRENTGROUP (), 1 )
    )
VAR CompaniesWithAllProducts =
    FILTER ( CompaniesWithNumProducts, [@Products] = NumOfSelectedProducts )
VAR Result =
    COUNTROWS ( CompaniesWithAllProducts )
RETURN
    Result

This measure should produce the appropriate Company Count.

In order to limit the list of companies in the visual on the lower right, you could add a visual-level filter using the above measure, set to "not blank" or ">0". 

A site note: One thing you may want to look at is whether all the bidirectional relationships are needed. Regardless, I believe this measure should still work (due to the many:1 relationships between company_field / field and company_field / company.

I'm hoping the above measure works, but please post back if needed.

Regards,

Owen

[Yachti]

Thank you so much for the solution. It works perfect
But how can I now show below the number on top which companies are shown? When I understand it correctly the multi-row card cannot show the measure because the multi-row expects an table and the measure only returns one data

[OwenAuger]

Glad to hear it 🙂

Regarding filtering the Multi-row card visual below the Company count:

You can keep it set up as a Multi-row card (or you could consider changing it to a Table), but what I meant was you need to add a visual-level filter using the newly-created measure.

Select the visual, drag the measure into the "Filters on this visual" section of the Filters pane, and set the filter to "is not blank".

This will limit the Companies shown to those that are related to all selected Products (i.e. the same companies counted in the measure itself).

Does that work for you?

Regards,

Owen

[Yachti]

It didn't work for me: 

Or did I made something wrong?

Regards,

Yachti

[OwenAuger · Accepted solution]

Hi again @Yachti ,

I took another look tested at my end, and the issue is actually to do with the bidirectional relationship between company_field and field.

When a particular company is filtered from the company table (as happens on each row of the Multi-row card), this filter propogates to field (due to the bidirectional relationship). This has the effect of reducing the number of products visible in the filter context to just those for that company, meaning every company that appears would appear to match all selected products.

To solve this you can either:

1. Change the relationship direction between company_field and field to "single" (this would be my preferred approach)
2. Change the measure as show below, with REMOVEFILTERS ( company ) added in the NumOfSelectedProducts variable:

Companies with all Products = 
VAR NumOfSelectedProducts =
    CALCULATE (
        COUNTROWS ( VALUES ( field[name] ) ),
        REMOVEFILTERS ( company )
    )
VAR CompaniesAndProducts =
    SUMMARIZE ( company_field, company[id], field[name] )
VAR CompaniesWithNumProducts =
    GROUPBY (
        CompaniesAndProducts,
        company[id],
        "@Products", SUMX ( CURRENTGROUP (), 1 )
    )
VAR CompaniesWithAllProducts =
    FILTER ( CompaniesWithNumProducts, [@Products] = NumOfSelectedProducts )
VAR Result =
    COUNTROWS ( CompaniesWithAllProducts )
RETURN
    Result

Regards,

Owen

[Yachti]

Thank you so much for helping me out. It nows works perfectly fine

[filipeoliveira]

Hi, if you want to filter companys based on Microsoft products' names, then I guess your relationships should be like this:

I should take a better look on the dataset to be sure about it, but try to make the changes described in the image one by one, starting from changing filter direction on "field" table.

[Yachti]

Thank you for your solutions but it still doesn't work like how I wanted it to work. It still shows all companies when at least one of the filter matches.

Here's also the updated version of the ERM