topic Re: Filter table based on an indepentend slicer in DAX Commands and Tips

Filter table based on an indepentend slicer

groetschel — Fri, 14 Jan 2022 16:50:36 GMT

Hey guys

I am trying to do something real simple, but I don't get it.

I am trying to create a new filtered table based on an existing one

Filtered Project Budget per Country = filter('Project Budget per Country','Project Budget per Country'[str_country]='Selector Table'[selected])

If I replace 'Selector Table'[selected] with "d2" everything works fine, but I would like to use a dynamic value of a slicer to filter the table.

So, I tried to create a helper table with

Selector Table = values('RSU BU SQL'[BU description])

and added a measure like this

selected = if(HASONEVALUE('RSU BU SQL'[BU description]),VALUES('RSU BU SQL'[country cod]),"xx")

If I create an element to show the [selected] value it shows the correct value for the filter "d2", but somehow the "Filtered Project Budget per Country" stays empty...

Please help...

Regards,

Gunnar

Re: Filter table based on an indepentend slicer

Greg_Deckler — Fri, 14 Jan 2022 18:37:24 GMT

groetschel Try:

Filtered Project Budget per Country = filter('Project Budget per Country','Project Budget per Country'[str_country]=MAX('Selector Table'[selected]))

Or you could use SELECTEDVALUE or if you want multiple use DISTINCT and IN

Re: Filter table based on an indepentend slicer

groetschel — Mon, 17 Jan 2022 19:00:44 GMT

This does not work and maybe I wasn't clear enough. That's why I created this simple example:

First table: BU with column "BU" values d2, nl, ru

Second table: Projects with column (lid, Days, Code, BU) values (1,100,JUHU,d2) and (2,80,RECO,d2)

Third table: ProjectBudget with column (fklid, BU, Budget) values (1,d2,20) and (1,ru,80)

Relations: BU[BU] -- Projects[BU], Projects[lid] -- ProjectsBudget[fklid]

I created a slicer for BU table.

And a table with columns: Projects[Days, BU, Code],ProjectBudget[Budget]

Selecting "d2" in the slicer should show the following:

[Days,BU,Code,Budget]

100,d2,JUHU,20 <-- and here is the challenge: The slicer should filter the table, but show only the ProjectBudget values for d2.

80,d2,RECO,

Please help.

PBIX file: https://kicg-my.sharepoint.com/:u:/g/personal/ggroetschel_kaizen_com/Ef16Ex_xAYJOriacxggAnIQBs4gAAo3FVjY43hgIl225pg?e=hxOdBd

Regards,

Gunnar

Re: Filter table based on an indepentend slicer

v-xiaotang — Thu, 20 Jan 2022 05:47:13 GMT

Hi groetschel

Please check the result bellow,

Best Regards,

Community Support Team _Tang

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

Re: Filter table based on an indepentend slicer

groetschel — Thu, 20 Jan 2022 20:51:24 GMT

It is working! But I cannot figure out, how you did it.

Could you explain, please?

Regards,

Gunnar

Re: Filter table based on an indepentend slicer

v-xiaotang — Tue, 25 Jan 2022 06:26:35 GMT

Hi groetschel

😁select this,

Best Regards,

Community Support Team _Tang

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