Dynamic Filtering Value With Different Conditions

[Anonymous]

Hai All Need your help on this please help me.

This is my target table attached below

Target Table
Group	Year	Target
India	2022	3
Japan	2022	3
Russia	2022	3
Ukraine	2022	3
UK	2022	2
FRANCE	2022	3

I am trying to find a value in single visual with different conditions
And Below i am attaching requirement Screenshot and I Explained 4 Conditions Aswell  please help me

Thanks

[Anonymous · Accepted solution]

Target 2 =
IF (

|| HASONEVALUE ( 'Mapping Area'[Cluster/Function] )
|| HASONEVALUE ( 'Calendar'[Year] ),
SWITCH (
TRUE (),
&& ISFILTERED ( 'Mapping Area'[Cluster/Function] )
&& ISFILTERED ( 'Calendar'[Year] ), SUM ( Targets[CLT/FLT TARGET 2 STARS] ),
|| ISFILTERED ( 'Mapping Area'[Cluster/Function] ), [FLT],
ISFILTERED ( 'Calendar'[Year] ), SUM ( Targets[CLT/FLT TARGET 2 STARS] )
),
IF ( CALCULATE ( NOT ISCROSSFILTERED ( 'Targets' ) ), [FLT] )
)

[Anonymous · Accepted solution]

Target 2 =
IF (

|| HASONEVALUE ( 'Mapping Area'[Cluster/Function] )
|| HASONEVALUE ( 'Calendar'[Year] ),
SWITCH (
TRUE (),
&& ISFILTERED ( 'Mapping Area'[Cluster/Function] )
&& ISFILTERED ( 'Calendar'[Year] ), SUM ( Targets[CLT/FLT TARGET 2 STARS] ),
|| ISFILTERED ( 'Mapping Area'[Cluster/Function] ), [FLT],
ISFILTERED ( 'Calendar'[Year] ), SUM ( Targets[CLT/FLT TARGET 2 STARS] )
),
IF ( CALCULATE ( NOT ISCROSSFILTERED ( 'Targets' ) ), [FLT] )
)

[Anonymous]

Hi @Anonymous ，

The sample data you gave does not have a column named FLT, is this a column that actually exists in the table or do I need to create a calculated column to get it?

Also, you mentioned that if 2022 is selected, only the data for 2 stars is aggregated. In that case, there is only one record that meets the requirement as shown below. So if the user selects another year, how will it be calculated?

Best Regards

[Anonymous]

Actually, I found the solution

[Anonymous]

Hi @Anonymous ,

It's glad to hear that your problem has been resolved. If it is convenient, would you like to share your solution here? And mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thanks for your time and sharing in advance.

Best Regards

[Anonymous · Accepted solution]

Target =
IF (

|| HASONEVALUE ( 'Mapping Area'[Cluster/Function] )
|| HASONEVALUE ( 'Calendar'[Year] ),
SWITCH (
TRUE (),
&& ISFILTERED ( 'Mapping Area'[Cluster/Function] )
&& ISFILTERED ( 'Calendar'[Year] ), SUM ( Targets[CLT/FLT TARGET 2 STARS] ),
|| ISFILTERED ( 'Mapping Area'[Cluster/Function] ), [FLT],
ISFILTERED ( 'Calendar'[Year] ), SUM ( Targets[CLT/FLT TARGET 2 STARS] )
),
IF ( CALCULATE ( NOT ISCROSSFILTERED ( 'Targets' ) ), [FLT] )
)