Filter table based on an indepentend slicer

[groetschel]

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

[groetschel]

Hi

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_xAYJOriacxggAnIQBs4gAAo3...

Regards,

Gunnar

[v-xiaotang · Accepted solution]

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.

[groetschel]

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

Could you explain, please?

Regards,

Gunnar

[v-xiaotang]

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.

[Greg_Deckler]

@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