DAX Support: Show specific string based on number of filters / or no filters

[Anonymous]

Dear forum, I hope you can help me once again

I will start by asking the question before showing my examples: 

How does one write an expression to show a specific text string, based on how many filters on a specific dimension level have been applied?  

I am working as controller for a large global organization with several factories and trying to develop a monthly report.
I am stuck on fixing this text window with three different text string measures, showing what factory/factory group has been filtered for. (A factory group is often called a cluster).

I have done a mock-table on what the filter/dimension table structure looks like:

Based on these three dimension fields that I used as a slicer in my report, I want to write three text string measures to show what Cluster / Sub Cluster / Factory is being filtered - but in a smart way which I'll explain below.

For example: if the slicer is set so that everything - (or, nothing) is filtered, I would like the measures to show this:

Cluster Measure shows: "Cluster - All"
Sub Cluster Measure shows: "Sub Cluster - All"

Factory Measure shows: "Factory - Multiple Selected"

Similarly, if "Select All" is used, same result.

But if we only want to look at Poznan factory, the 3 measures should show the following:

But if we look at both Poznan and Madrid factory, I want to be able to show name of both sub-clusters, but the factory name should say "multiple factories". Like below:

Any advice on this kind of DAX would be greatly appreciated.
I have thought about SELECTEDVALUE, ISFILTERED and other DAX but realized that I need to combine them with COUNT and I have got lost in the exercise.

/Rasmus 

[amitchandak]

@Anonymous , Try a measure like

measure = "Cluster : " &
Switch(true(),
not(isfiltered(Table[Cluster])), "All",
countx(allselected(Table),Table[Cluster]) =1, selectedvalue(Table[Cluster]) ,
"Multiple Selected"
)

You can add other like this as per need

[Anonymous]

COUNTX(

ALLSELECTED(FactoryTable),FactoryTable[Cluster]) =1,

SELECTEDVALUE(FactoryTable[Cluster]),

This part is not working, probably because ALLSELECTED ignores that it is CLUSTER LEVEL.
If I only select a Factory Unit, the filter is correct.

Any idea @amitchandak if there is a different way to count how many have been selected on the higher level?

[Anonymous · Accepted solution]

Found a solution with some googling:

Replaced the ALLSELECTED middle part of the expression with:

COUNTROWS(FILTERS(FactoryTable[Cluster]))=1

Thanks anyway! 

[Anonymous]

Hi Amit,

Thanks for your reply.

It is almost working - "All" and "Multiple" are going well. But if I just select 1 cluster, I still get "Multiple", which means that the DAX probably does not recognize the part of counting selected = 1. 

I tried adding an "IF" (IF(COUNTX.... =1) but that did not help. What do you think can be the issue? 

[Anonymous]