Forum Discussion

Th0mc's avatar
Th0mc
Frequent Visitor
3 years ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Th0mc
      Frequent Visitor
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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