cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## How to filter related table by measure?

Hello! I`m very-very new to Power Bi and, unfortunately, I got stuck with this issue. I have three tables (two tables with facts (costs and traffic) and one table with kyes (for filtering). I`m trying to write a measure, which will show a sum of spent money (a

facts_ads_costs[adCost] field) but only if there is at least one session in table facts_GA_traffic.

And this variant I wrote dies not work.

amount_spent =
VAR sessions = SUM(facts_GA_traffic[sessions])
RETURN

1 ACCEPTED SOLUTION
Community Support

key_utm_from_BQ   to facts_GA_traffic :one to many(1:*)

facts_GA_traffic  and  facts_ads_costs , not with direct relatinship.

So you need to show the relationship throuth dax.

And according your picture ,I create a sample:

key_utm_from_BQ :

facts_GA_traffic:

Relationship:

``````amount_spent =
VAR sessions =
SUM ( facts_GA_traffic[sessions] )
RETURN
``````

Return :

And based on the three table ,try following steps:

Step1, create a measure on facts_GA_traffic:

``````session1 = SUM(facts_GA_traffic[sessions])
``````

Step 2, create measure on  facts_ads_costs:

``````amount_spent =

Get result(the figure below, only medium=3 satisfies the condition):

Best Regards

Lucien

2 REPLIES 2
Community Support

key_utm_from_BQ   to facts_GA_traffic :one to many(1:*)

facts_GA_traffic  and  facts_ads_costs , not with direct relatinship.

So you need to show the relationship throuth dax.

And according your picture ,I create a sample:

key_utm_from_BQ :

facts_GA_traffic:

Relationship:

``````amount_spent =
VAR sessions =
SUM ( facts_GA_traffic[sessions] )
RETURN
``````

Return :

And based on the three table ,try following steps:

Step1, create a measure on facts_GA_traffic:

``````session1 = SUM(facts_GA_traffic[sessions])
``````

Step 2, create measure on  facts_ads_costs:

``````amount_spent =

Get result(the figure below, only medium=3 satisfies the condition):

Best Regards

Lucien

Super User

I think this does what you're trying to achieve:

``````amount_spent =
VAR sessions = SUM(facts_GA_traffic[sessions])

RETURN

IF( sessions > 1,
BLANK()
)``````

What you were doing wouldn't work a row in facts_ads_costs has no sessions column to filter on.

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors