Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bendsteel6
Frequent Visitor

Filter with Multiple Contains and 'Not Contains'

Hi,

I'm trying to create a measure that will show me the number of records where:

 

OTHER_COST_DESC contains either '23917401' or '24546941' but DOES NOT contains 'FREIGHT'.

 

I'm trying to use this:

Num_EGR_CLAIMS =

CALCULATE(COUNTROWS(
FILTER(OTHER_COSTS,CONTAINSSTRING(OTHER_COSTS[OTHER_COST_DESCRIPTION],"23917401")

||CONTAINSSTRING(OTHER_COSTS[OTHER_COST_DESCRIPTION],"24546941")),
FILTER(OTHER_COSTS,NOT(CONTAINSSTRING(OTHER_COSTS[OTHER_COST_DESCRIPTION],"FREIGHT")))
)

 

but I get errors in the 'not contains' part.  I've tried so many different combinations but cannot get it ot work.  Any suggestions?

 

Thanks!

1 ACCEPTED SOLUTION
NishPatel
Resolver II
Resolver II

Please try below measure,

Num_EGR_CLAIMS =
CALCULATE (
COUNTROWS (
FILTER (
OTHER_COSTS,
OTHER_COSTS[OTHER_COST_DESCRIPTION] <> "FREIGHT"
&& ( OTHER_COSTS[OTHER_COST_DESCRIPTION] = "23917401"
|| OTHER_COSTS[OTHER_COST_DESCRIPTION] = "24546941" )
)
)
)

View solution in original post

2 REPLIES 2
bendsteel6
Frequent Visitor

Yes!  It works!  Thank you @NishPatel.  I've accepted this as the solution.

NishPatel
Resolver II
Resolver II

Please try below measure,

Num_EGR_CLAIMS =
CALCULATE (
COUNTROWS (
FILTER (
OTHER_COSTS,
OTHER_COSTS[OTHER_COST_DESCRIPTION] <> "FREIGHT"
&& ( OTHER_COSTS[OTHER_COST_DESCRIPTION] = "23917401"
|| OTHER_COSTS[OTHER_COST_DESCRIPTION] = "24546941" )
)
)
)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.