The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@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 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |