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], depending on:

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

 

Any help is very wellcome.

Thanks!

Rbn

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

4 Replies

  • 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