use CONCATENATEX to show "all" or the selected values, but limited by groupe filters and by revenue

[Rygaard]

OK -  so i made this little messure that shows selected departments and it works great it either show "all" or the departments selected.

'Selected department = 
Var seldep=
CONCATENATEX(ALLSELECTED(Departments[D.No+D.Name]),Departments[D.No+D.Name],", ")
var IsItFiltered=
    IF (ISFILTERED(Departments[D.No+D.Name]),seldep,"ALL")
return
IsItFiltered

BUT I only wanted it to show departments that has Revenue in the period i selected.

ALSO it would be nice if it did not show ALL if i made a selection on the Regions Filter.

so i tried wrapping it in a Calculate with a filter on value Entry, must be bigger than 1 this did not work...  any sugestions `?

[kadirkkkk]

Nop. This also did not work to filter the concat value by the slicer.

[Greg_Deckler]

@Rygaard Hard to tell exactly without having source data and expected output to test with. Seems like you are going for a dynamic title, this may give you some more ideas:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Most-Amazing-Mind-Blowing-Dynamic-Slicer...

[Rygaard]

@Greg_Deckler - Great post, but id do not cover what im asking....

I have the following tables country, Region, Department, value entry, calender (and 20 others that dont matter for this)

So when i select UK and US i dont want to see German departments

In the slicers its easy i just put a filter on revenue from the Value Entry - this way the slicer only show Departmetns in selected country(S)

But i cant put a filter on this messure that is dynamic non dynamic it work fine like :

 testResult23 =CALCULATE(CONCATENATEX(VALUES(Departments[D.No+D.Name]),Departments[D.No+D.Name],", "),FILTER(CountryTable,CountryTable[Contry_DW_Account] in {"UK", "US"}))

but if i try to replace the {"uk", "US"} with a variable it wont work.

or if i try to use '

test = CALCULATE(CONCATENATEX(VALUES(Departments[D.No+D.Name]),Departments[D.No+D.Name],", "),SELECTEDVALUE(CountryTable[Country_DW_Account])) 

im sure ther is a way to do this I just have not found it yet

[Anonymous]

@Rygaard 

Try this one:

test = CALCULATE(CONCATENATEX(VALUES(Departments[D.No+D.Name]),Departments[D.No+D.Name],", "),
FILTER(CountryTable,CountryTable[Contry_DW_Account] = SELECTEDVALUE(CountryTable[Country_DW_Account])))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.