Find average of filtered key word from on table across another linked table in new measure

[grudginluv20]

In powerBI, i have a table called Nodes.

Within that table there are string columns called caption with a list of names.

I have a second table called ResponseTime,

within that table I have column called Availability that are populated with interger values for availability .

The two tables Nodes and ResponseTime are linked with primary key NODE_ID.

Each caption from nodes table has a reference of availability value from ResponseTime table.

I want to create a measure that filters out only caption values that contains 'pub-rt' and computes the average of all caption values of that contain 'pub-rt' with their average availability value.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result.

[v-xiandat-msft · Accepted solution]

Hi @grudginluv20 ,

Below is my Node table:

Below is my ResponseTime Table:

The following DAX might work for you:

AverageAvailabilityForPubRT = 
CALCULATE(
    AVERAGE(ResponseTime[Availability]),
    FILTER(
        Nodes,
        CONTAINSSTRING(Nodes[Caption], "pub-rt")
    ),
    USERELATIONSHIP(Nodes[NODE_ID], ResponseTime[NODE_ID])
)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grudginluv20]

Thank you! This totally solved my problem.

As a follow up question, with the same principle, how get the average value for 'pub-rt' and 'bbb' together as one.