If field contains text in N:1 related table

[Brin2040]

Doing a custom column "Has Blockable Connector" in the PowerApps table of the Power Platform CoE Starter Kit's Power BI report. The PowerApps table has a 1:N relationship with the App Connection Reference table (one app can have many connections). The goal is to indicate whether any one of the multiple connectors an app may have is blockable by a Power Platform DLP policy.

The App Connection Reference table has a "DLP Blockable" field with values of either "Blockable" or "Not Blockable". What is the syntax for a custom column in the PowerApps table that would look at the DLP Blockable field of the App Connection Reference table and return a value for yes, among the multiple records of connections for that specific app, at least one of them is blockable, or no, none of them is blockable?

Data model:

App Connection Reference table:

In the example above, the app ending in -4ec78aa7ff49 in the PowerApps table would have a value of "Yes" in the Has Blockable Connector column because one of its four connectors is blockable.

[Brin2040]

@TomMartensworks beautifully and very clearly explained. Thank you!

[TomMartens · Accepted solution]

Hey @Brin2040 ,

you can create the calculated column "Has Blockable Connector" in the table "Power Apps" using the below DAX:

Has Blockable Connector = 
var noOfBlockableConnectors = COUNTROWS( FILTER( RELATEDTABLE( 'App Connection Reference' ), 'App Connection Reference'[Is Blockable] = "Blockable" ) )
return
IF( noOfBlockableConnectors > 0 , "yes" , "no" )

This is how my model looks like:

The content of the table "App Connection Reference":

And the result of the calculated column in the table Power Apps:

Hopefully, this provides what you are looking for.

Regards,

Tom