Forum Discussion
Creating a flag based on text values from 2 different tables
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.
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.
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?
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.
