Forum Discussion
MJG2112
Advocate II
Advocate IILooking 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 man...
Hi,
Try this measure
Issue = if(CALCULATE(COUNTROWS(Data),FILTER(values(Data[Colour]),CONTAINSSTRING(Data[Comments],"UNKNOWN" )))>0,"Yes","No")
MJG2112Advocate II
Advocate IIZanqueta 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
Super User
Super UserHi 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 🌀.