switch depending on the slicer selection

[Reuben]

Hi all,

I have two measure:

[CompanyTarget]

[SiteTarget]

And one slicer:

"Sellect All" / "Site 1" / "Site2" /... / "Site8"

I would like to swich from [CompanyTarget] to [SiteTarget], depending on:

If I select "Sellect All" then return  [CompanyTarget] otherwise [SiteTarget]

Any help is very wellcome.

Thanks!

Rbn

[v-shex-msft]

HI @Reuben ,

You can try to use the following measure to switch between different measure result based on slicer selection:

Measure =
SWITCH ( ISFILTERED ( Selector[Site] ), [SiteTarget], [CompanyTarget] )

If above not help, can you please share some sample data for test?
Regards,

Xiaoxin Sheng

[Reuben]

Hi @v-shex-msft  thank you very much for your reply and sorry for not answering you before, but I've been travelling with no Access to my computer.

I have tried your formula but I think that I am doing something wrong.

Here you Will find a simplified pbix of my model test target 

Just a Little bit more information:

Table 1 (Targets) : Contains water consumption targets by site code and date.

Date: is the first day of the fiscal year. The target applies for the whole year.

Site code: 99 is a ficticia code, which represents the total company but its not the sum of the individual sites

Table 2 (Sites): Site code and site name

You Will notice that 99 is not in the table. Why? It is a direct query from OCDB so only contains the real sites.

So what I want do do is:

If I select an individual site with the slicer, then return the target for this site: Example: 2020, Site 1 -> 3,0987

If I select all sites, the return the target allocated in 99: Example: 2020, Sellected All -> 2,376

Thanks again for your help

[v-shex-msft · Accepted solution]

Hi @Reuben ,

I fixed my formula and your measures, then it can work with both single and multiple selections:

Site Target =
SELECTEDVALUE ( Targets[Water], SUM ( Targets[Water] ) )

Company Target =
CALCULATE (
    SUM ( Targets[Water] ),
    FILTER ( ALL ( Targets ), Targets[Site Code] = 99 )
)

Measure = 
SWITCH (
    ISFILTERED( Tabla[Site Name] ),
    TRUE (), [Site Target],
    FALSE (), [Company Target]
)

Regards,

Xiaoxin Sheng

[Reuben]

Great solution @v-shex-msft  thank you very much!