topic Re: DAX expression with filtered text in DAX Commands and Tips

DAX expression with filtered text

texasmcse — Wed, 07 Dec 2022 18:14:55 GMT

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 ))
)

Re: DAX expression with filtered text

Greg_Deckler — Wed, 07 Dec 2022 19:32:56 GMT

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 )) ) )

Re: DAX expression with filtered text

texasmcse — Wed, 07 Dec 2022 20:57:34 GMT

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.

Re: DAX expression with filtered text

texasmcse — Thu, 08 Dec 2022 12:50:43 GMT

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.

Re: DAX expression with filtered text

texasmcse — Fri, 09 Dec 2022 16:33:57 GMT

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 ))
)

Re: DAX expression with filtered text

texasmcse — Tue, 13 Dec 2022 12:40:28 GMT

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