Forum Discussion

texasmcse's avatar
texasmcse
Frequent Visitor
3 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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 ))
)
)
    • texasmcse's avatar
      texasmcse
      Frequent Visitor
      3 years ago

      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.  

  • texasmcse's avatar
    texasmcse
    Frequent Visitor
    3 years ago

    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.  

    • texasmcse's avatar
      texasmcse
      Frequent Visitor
      3 years ago

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

      • texasmcse's avatar
        texasmcse
        Frequent Visitor
        3 years ago

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