1 Slicer for 1 Filter and 1 opposite filter applied

[Samkar78]

Hi,

In a financial reports on Intercompany accounting I have 1 slicer that drives the choice on  the Company.

With this same slicer I would like to remove the lines that have the same Intercompany code (INTERCO_CD) as the Company Code selected.

I tried to create a measure and to applied the second filter with it on the table.

test filter =

SWITCH(

    TRUE(),

    ISFILTERED('COMPANY'[COMPANY_CD]) && VALUES('GL'[INTERCO_CD])  IN  VALUES ('COMPANY'[COMPANY_CD]),

    0,1)

It does not work... ☹️

The final goal is to have a buton that activate this filter or not.

many thanks for your suggestion

[Samkar78]

Thanks @Anonymous this solution works but... I can't use the orginal slicer which is a hierarchy slicer. I have to click the COMPANY_CD of the unlinked table.
I continue to look for an acceptable solution for users.

[Samkar78]

thanks @Anonymous  that's half of the expected result 😉 you exclude the selected Company Code.

the expected result is this one,  based on you example :

The goal is to have 1 Slicer that combine 2 filters :

• the standard one that select the right Company code
• the opposite one (what you did) that exludes the INTERCO_CD based on the chosen company in the main slicer 

[Anonymous · Accepted solution]

Hi @Samkar78 ,

Try this.

Measure = 
VAR _company_cdForSlicer = VALUES('Table 2'[COMPANY_CD])
VAR _interco_cd = SELECTEDVALUE('Table'[INTERCO_CD])
VAR _compant_cd = SELECTEDVALUE('Table'[COMPANY_CD])
RETURN 
 IF( NOT ISFILTERED('Table 2'[COMPANY_CD]),
 SUM('Table'[AMOUNT]),
    IF( NOT _compant_cd IN _company_cdForSlicer,BLANK(),
        IF(_interco_cd IN _company_cdForSlicer,BLANK(),SUM('Table'[AMOUNT]))
    )
 )

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @some_bih ,Thanks for your quick reply, I will add more.

Hi @Samkar78 ,

The Table data is shown below:

Please follow these steps:

1. Use the following DAX expression to create a table

Table 2 = VALUES('Table'[COMPANY_CD])

2.Put field into slicer.

3.Use the following DAX expression to create a measure

Measure = 
VAR _company_cd = VALUES('Table 2'[COMPANY_CD])
VAR _interco_cd = SELECTEDVALUE('Table'[INTERCO_CD])
RETURN 
 IF( NOT ISFILTERED('Table 2'[COMPANY_CD]),
 SUM('Table'[AMOUNT]),
    IF(_interco_cd IN _company_cd,BLANK(),SUM('Table'[AMOUNT]))
 )

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samkar78]

@some_bih The data model is pretty simple on this part. I got a fact table 'GL' and a dimension table 'COMPANY'

If add a new column Yes/No based on inter company code , I will remove ALL inter company lines and not specifically the chosen company selected in the Company Slicer.

[some_bih]

Hi @Samkar78 I realised that I did not put link for bookbarks, check link if could be usefull in your case (check part Assign bookmarks to buttons)

[some_bih]

Hi @Samkar78 based on your data, without model and other details, possible solution

- as you mentioned button is to use bookmark features in Power BI, check link or

- create separate column for intercompany flag with yes / no values (I am reporter for Intercompany :))