How to filter related table by measure?

[Notbad]

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
CALCULATE(SUM(facts_ads_costs[adCost]), sessions > 1)

Could you please help? Thanks!

[v-luwang-msft · Accepted solution]

Hi @Notbad ,

Accourding your picture ,the relationship in your tables:

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

key_utm_from_BQ   to facts_ads_costs: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:

facts_ads_costs:

Relationship:

your measure:

amount_spent =
VAR sessions =
    SUM ( facts_GA_traffic[sessions] )
RETURN
    CALCULATE ( SUM ( facts_ads_costs[adCost] ), sessions > 1 )

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 = 
CALCULATE(SUM(facts_ads_costs[adCost]),FILTER(facts_GA_traffic,facts_GA_traffic[medium]=MAX(facts_ads_costs[medium])&&facts_GA_traffic[session1]>1))

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

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[bcdobbs]

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

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

RETURN

IF( sessions > 1,
	SUM(facts_ads_costs[adCost]),
	BLANK()
)

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