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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
texasmcse
Frequent Visitor

DAX expression with filtered text

My goal is to create a card visual that shows the number of rows in a database that have a specific word in a field.  Example below from an MECM database looking for value '11'.  

 

That works, I just can't figure out how to store that number (the number of rows with the value of '11') into a measure and include it in a card visual.  Any help appreciated

 

 

EVALUATE
SUMMARIZECOLUMNS(
    'From MECM DB'[Operating System],
    KEEPFILTERS( FILTER( ALL( 'From MECM DB'[Operating System] ), SEARCH( "11", 'From MECM DB'[Operating System], 1, 0 ) >= 1 ))
)

5 REPLIES 5
texasmcse
Frequent Visitor

@Greg_Deckler  thanks!it's a step closer but I think it's doing a distinct count.  Changed the query to use OS and should find several devices with 'Enterprise' in the field and it shows 3, which matches the 3 different OS values (win11, win10, win11N).  There is an easier way to do this (slicer) but I'm going to apply what I learn here to a more complex scenario where I'm finding specific error messages in description fields.  devicecount.png

Can anyone help me figure out how to make the query not do a distinct count?  I need the query to return all the rows that match the search so I can use it as a measure for a card visual.  I've spent alot of time and just don't have the DAX skills yet to figure it out.  thanks!

 

EVALUATE
SUMMARIZECOLUMNS(
    MECM[OS],
    KEEPFILTERS( FILTER( ALL( MECM[OS] ), SEARCH( "11", MECM[OS], 1, 0 ) >= 1 ))
)

still trying to figure this out.  does anyone have any guidance?  thanks

 

Greg_Deckler
Community Champion
Community Champion

@texasmcse Maybe:

Measure = 
  COUNTROWS(
SUMMARIZECOLUMNS(
    'From MECM DB'[Operating System],
    KEEPFILTERS( FILTER( ALL( 'From MECM DB'[Operating System] ), SEARCH( "11", 'From MECM DB'[Operating System], 1, 0 ) >= 1 ))
)
)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler   If SUMMARIZECOLUMNS only returns distinct values, what function would return all values and work in my scenario?  Basically the equivalent of removing DISTINCT from a TSQL query.  

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.