Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I want to filter value NOT CONTAINS the specified value but its not working
Mismatch Executive =
CALCULATE( COUNTROWS('table'),
FILTER(
'table',
('table'[Position Assignment Category] = "Permanent ~ Executive" ||
'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive")
&& ('table'[Job Grade] <> BLANK()) &&
( NOT (
CONTAINS ( 'table','table'[Job Grade], "P1" )
|| CONTAINS ( 'table','table'[Job Grade], "P2" )
|| CONTAINS ( 'table','table'[Job Grade], "P3" )
))
)
)+0
can you help me?
Solved! Go to Solution.
Hi, @zahidah_mabd
Please try formula as below:
Mismatch Executive =
CALCULATE (
COUNTROWS ( 'table' ),
FILTER (
'table',
( 'table'[Position Assignment Category] = "Permanent ~ Executive"
|| 'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive" )
&& ( 'table'[Job Grade] <> BLANK () )
&& ( NOT ( 'table'[Job Grade] IN { "P1", "P2", "P3" } ) )
)
) + 0
Best Regards,
Community Support Team _ Eason
Can I use CONTAINS instead of CONTAINSSTRING?
Hi, @zahidah_mabd
Please try formula as below:
Mismatch Executive =
CALCULATE (
COUNTROWS ( 'table' ),
FILTER (
'table',
( 'table'[Position Assignment Category] = "Permanent ~ Executive"
|| 'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive" )
&& ( 'table'[Job Grade] <> BLANK () )
&& ( NOT ( 'table'[Job Grade] IN { "P1", "P2", "P3" } ) )
)
) + 0
Best Regards,
Community Support Team _ Eason
@zahidah_mabd , Try like
Mismatch Executive =
CALCULATE( COUNTROWS('table'),
FILTER(
'table',
('table'[Position Assignment Category] = "Permanent ~ Executive" ||
'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive")
&& not(Isblank('table'[Job Grade] )) &&
( NOT (
CONTAINSSTRING ( 'table','table'[Job Grade], "P1" )
|| CONTAINSSTRING ( 'table','table'[Job Grade], "P2" )
|| CONTAINSSTRING ( 'table','table'[Job Grade], "P3" )
))
)
)+0