Creating a flag based on text values from 2 different tables

[Oceanbagel]

I have two tables that are related based on ID. This is a many to many relationship.

TableA

ID	City
101	Fremont
101	Fremont
102	Lakeside
103	Auburn
104	Colburn
105	Ephrata
106	Columbus
107	Eugene

TableB

ID	Narrative
101	misc narrative text
102	misc narrative text
103	misc narrative text
103	misc narrative text including the word danger
104	misc narrative text
105	misc narrative text including the word help
106	misc narrative text
107	misc narrative text including the word critical

I need to create a flag column in TableA (or in a new table) that returns a true/false based on criteria found in either table:

If TableA[City] is Fremont or Columbus or if TableB[Narrative] contains danger, help, or critical return true. Otherwise return false.

This is obviously sample data and my true application involves several criteria from both columns that I need to return true. I'm able to apply the City and Narrative logic separately within their respective tables but bringing them together is where I'm stuck.

[parry2k]

@Oceanbagel if you have more keywords to search I would recommend adding a column in PQ in TableB for the matching keywords with flag 1 and 0 so that you don't have to check all the keywords in the DAX, it will make things super clean and simpler, and it is super easy to add a column in PQ.

[AmiraBedh · Accepted solution]

You're looking for a way to combine criteria from two tables to generate a flag column. In this context, you can make use of DAX in Power BI to create such a flag.

The basic process can be split into:

1. Create relationships between the tables based on ID.
2. Use the RELATED function (or RELATEDTABLE function if there are multiple related rows) to pull data from TableB into TableA.
3. Create the flag based on the criteria.

Flag Column =
VAR CurrentCity = TableA[City]
VAR RelatedNarrative = CALCULATE(CONCATENATEX(TableB, TableB[Narrative], ", "), ALL(TableB[ID]))

RETURN
IF(
(CurrentCity IN {"Fremont", "Columbus"} ||
SEARCH("danger", RelatedNarrative, 1, 0) > 0 ||
SEARCH("help", RelatedNarrative, 1, 0) > 0 ||
SEARCH("critical", RelatedNarrative, 1, 0) > 0),
TRUE(),
FALSE()
)

This is how I am explaining what I have done so far :

1. `VAR CurrentCity`: This gets the city from the current row in TableA.
2. `VAR RelatedNarrative`: This calculates a concatenated narrative from all the rows in TableB related to the current ID in TableA. We use CONCATENATEX to concatenate all related narratives, and we reset the filter context on the ID column to make sure we get all related rows.
3. `RETURN`: We then use an IF statement to evaluate our conditions:
- If the city is either "Fremont" or "Columbus".
- If the related narrative contains the word "danger", "help", or "critical".

The flag is then set to TRUE if any of the conditions are met, otherwise it's set to FALSE.

[Oceanbagel]

This worked. Thank you very much!

How would you filter out row of narrative based on a similar search to prevent them from getting to the Return step?

[AmiraBedh]

If you want to filter out certain rows from `TableB` before evaluating them in the `RETURN` step, you can modify the formula in the `RelatedNarrative` variable.

Let's assume you want to filter out any rows in `TableB` where the narrative contains the word "exclude" (you can adjust the criteria as needed):

Flag Column =
VAR CurrentCity = TableA[City]
VAR RelatedNarrative =
CALCULATE(
CONCATENATEX(
FILTER(
TableB,
SEARCH("exclude", TableB[Narrative], 1, 0) = 0
),
TableB[Narrative],
", "
),
ALL(TableB[ID])
)

RETURN
IF(
(CurrentCity IN {"Fremont", "Columbus"} ||
SEARCH("danger", RelatedNarrative, 1, 0) > 0 ||
SEARCH("help", RelatedNarrative, 1, 0) > 0 ||
SEARCH("critical", RelatedNarrative, 1, 0) > 0),
TRUE(),
FALSE()
)

In the `RelatedNarrative` variable, the `FILTER` function filters out rows from `TableB` where the narrative contains the word "exclude". Then, the remaining narratives are concatenated into a single string.

So, by the time we get to the `RETURN` step, any narratives containing "exclude" have already been removed and won't be evaluated in the flag-setting logic.

[parry2k]

@Oceanbagel try following as column in TableA

Match Found = 
VAR __TableB = 
ADDCOLUMNS ( 
    RELATEDTABLE ( TableB ), 
    "@Matched", INT ( 
        CONTAINSSTRING ( TableB[Narrative], "Danger" ) || 
        CONTAINSSTRING ( TableB[Narrative], "Help" ) || 
        CONTAINSSTRING ( TableB[Narrative], "Critical" ) 
    ) 
) 
VAR __MatchFound = SUMX ( __TableB, [@Matched] )
RETURN
TableA[City] IN { "Columbus", "Fremont" } && NOT ISBLANK ( __MatchFound )