Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

switch depending on the slicer selection

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], dependi...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Anonymous ,

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous  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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Great solution Anonymous  thank you very much!