Forum Discussion

MJG2112's avatar
MJG2112
Icon for Advocate II rankAdvocate II
8 months ago
Solved

Looking Up a Partial Value in a Column

Hi.  I'm trying to do something that on the face if it seems simple enough, but I'm struggling.  I've had to create some simple mock data as I can't share the real thing.  I have a visual showing many columns from many tables, but one requires finding a partial value from a table.  The simplified visuals hopefully help explain.  Month is selected via a slicer.  What I need to do is report a Yes or No against each Product if there is any row for the Month and Product containing the word UNKNOWN in the Comments column.  It doesn't matter which COLOUR it's against or how many times it appears, as long as it's at least once.

 

 

 

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 months ago

    Hi,

    Try this measure

    Issue = if(CALCULATE(COUNTROWS(Data),FILTER(values(Data[Colour]),CONTAINSSTRING(Data[Comments],"UNKNOWN" )))>0,"Yes","No")

8 Replies

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Icon for Memorable Member rankMemorable Member
    8 months ago

    MJG2112 You should be able to use CONTAINS, like:

    Contains Unknown = CONTAINS( 'Data', [Comments], "UNKNOWN" )

    Or maybe:

    Contains Unknown = CONTAINSSTRING( CONCATENATEX( 'Data', [Comments], "," ), "Unknown" )
  • Zanqueta's avatar
    Zanqueta
    Icon for Super User rankSuper User
    8 months ago

    Hi MJG2112,

     

    If your visual already has Month and Product in the context, you can simplify:
     
    HasUnknown =
IF(
    CONTAINSSTRING('YourTable'[Comments], "UNKNOWN"),
    "Yes",
    "No"
)
     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    8 months ago

    Please try the formula below:

     

    Product Issue =
VAR HasUnknown =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table'[Colour] ),
            CONTAINSSTRING ( 'Table'[Comments], "UNKNOWN" )
        )
    )
RETURN
IF ( HasUnknown > 0, "Yes", "No" )

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 months ago

    Hi,

    Try this measure

    Issue = if(CALCULATE(COUNTROWS(Data),FILTER(values(Data[Colour]),CONTAINSSTRING(Data[Comments],"UNKNOWN" )))>0,"Yes","No")
  • MJG2112's avatar
    MJG2112
    Icon for Advocate II rankAdvocate II
    8 months ago

    Zanqueta GeraldGEmerick My visual is based on a different table (A) containing the Month and Product.  It takes values from other tables also containing Month and Product.  In this case if I create a calculated column on the main table (A) then I cannot reference the other table (B) using the CONTAINSSTRING.  Am I missing something?

    • Zanqueta's avatar
      Zanqueta
      Icon for Super User rankSuper User
      8 months ago

      Hi MJG2112, thank you for feedback. 

      If there is no relationship, we must capture the current context from TableA and apply it manually:
      HasUnknown =
VAR CurrentMonth = SELECTEDVALUE('TableA'[Month])
VAR CurrentProduct = SELECTEDVALUE('TableA'[Product])
VAR RowsWithUnknown =
    CALCULATE(
        COUNTROWS('TableB'),
        FILTER(
            'TableB',
            'TableB'[Month] = CurrentMonth &&
            'TableB'[Product] = CurrentProduct &&
            CONTAINSSTRING('TableB'[Comments], "UNKNOWN")
               )
    )
RETURN
RowsWithUnknown

       

      Best Practice

      • If possible, create a relationship between TableA and TableB. This simplifies the DAX and improves performance.
      • For large datasets or when performance is critical, it is often better to implement this logic in Power Query rather than in DAX.
        In Power Query, you can:
        • Add a column using Text.Contains([Comments], "UNKNOWN") to flag rows.
        • Group by Month and Product to create an aggregated indicator (e.g., “Yes” if any row contains "UNKNOWN").
        • Load this aggregated table and relate it to TableA.

       

      If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

      Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.