Forum Discussion
switch depending on the slicer selection
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
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
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
Great solution Anonymous thank you very much!