Need to dynamically filter on two columns adding the filter (OR operator)

[Th0mc]

Hi all,

Seems that my DAX knowledge is not sufficient.

Here are the needs :

- I want to filter a table depending on a visual slicer

- The value selected in this slicer combines a list of values for two columns of my table

- I want to filter whatever the condition is fulfiled for column1 or column2

Here is more details on a simple example :

- List of 'Domain Group' combining values to filter on SubDomain Code and Entity 3

- A sample of projects info that I want to filter

I created a slicer on 'Domain Group'.

I created a DAX column with the following formulae :

Filter on Domain+Entity = IF(Projects[Entity 3] IN VALUES('Domain Groups'[Entity 3]) || Projects[SubDomain Code] IN VALUES('Domain Groups'[SubDomain Code]), TRUE(),FALSE())

I filter the page by 'Filter on Domain+Entity' = True

When I filter on 'Engineering & Design', I expect to have the following result

When I filter on 'Finance & SAP', I expect to have the following result 

But in real life, the filter doesn't work at all, I have always all the table.

I have done a pbix sample file but I'm not able to share it 😞

Thanks for your help.

Thomas

[Anonymous]

Hi @Th0mc ,

- I want to filter a table depending on a visual slicer

Is this table a table of the model or a table visual? If it is a table in the model, it does not interact with the slicer, it only recalculates on refresh. 

If it's table vision, you can share the sample files, which is useful. Thanks in advance. You can choose to upload the file to cloud storage and post the sharing link here.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Th0mc]

Hi @Anonymous 

Yes, it is about filtering Table or Matrix visual.

The tricky part is that I need to filter with conditions on 2 columns 'SubDomain Code' and 'Entity 3' but 'adding' the conditions (OR) depending on another table.

I tried through a calculated column (DAX) because I though that it was dynamicaly calculated depending on filters in visuals but it is just the same as a column calculate in M, recalculated on refresh only. At least I learnt something ;).

But I'm still unable to make that filter works :(.

I tried to share through OneDrive but my company doesn't authorize sharing outside.

Here you should be able to have the tiny pbix I did to illustrate : https://filetransfer.io/data-package/xC4Yzb5j#link

Thx in advance

Thomas

[Anonymous · Accepted solution]

Hi @Th0mc ,

Please try:

Filter on Domain+Entity = 
VAR _domain_group =
    SELECTEDVALUE ( 'Domain Groups'[Domain Group] )
VAR _subdomain_code =
    CALCULATETABLE (
        VALUES ( 'Domain Groups'[SubDomain Code] ),
        'Domain Groups'[Domain Group] = _domain_group
    )
VAR _entity3 =
    CALCULATETABLE (
        VALUES ( 'Domain Groups'[Entity 3] ),
        'Domain Groups'[Domain Group] = _domain_group
    )
VAR _filter =
    IF (
        MAX ( 'Projects'[SubDomain Code] )
            IN _subdomain_code
                || MAX ( 'Projects'[Entity 3] ) IN _entity3,
        1
    )
RETURN
    _filter

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data