topic Re: 1 Slicer for 1 Filter and 1 opposite filter applied in DAX Commands and Tips

1 Slicer for 1 Filter and 1 opposite filter applied

Anonymous — Thu, 27 Jun 2024 12:48:32 GMT

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

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

some_bih — Fri, 28 Jun 2024 06:07:53 GMT

Hi Anonymous 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 :))

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

Anonymous — Fri, 28 Jun 2024 08:28:33 GMT

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.

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

some_bih — Fri, 28 Jun 2024 08:46:46 GMT

Hi Anonymous 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)

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

Anonymous — Fri, 28 Jun 2024 09:17:13 GMT

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

Hi Anonymous ,

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.

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

Anonymous — Fri, 28 Jun 2024 09:55:28 GMT

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

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

Anonymous — Fri, 28 Jun 2024 09:58:28 GMT

Hi Anonymous ,

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.

Re: 1 Slicer for 1 Filter and 1 opposite filter applied

Anonymous — Fri, 28 Jun 2024 14:17:18 GMT

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.