Reference Rows from One Table to Another

[Anonymous]

Hi Guys. 

Starting off with apologies if my question does not provide enough information. I have only just started using Power BI (since last week) so still pretty new with most

Basically,I have two tables, the first one is the targets table and the second one is actuals. The reason they are in separate tables is because the data is being fed from different sources. 

These tables are connected via Many to One relationship. (Many in Actuals to One in Targets). 

In the targets table, I am trying to input a colum which calculates the expected result for the month. 

So basically, if 40% of month has passed, sites are expected to have achieved 40% of their monthly targets. 

The graph looks into past 3 months worth of data. So for previous months, the expected result will be = Targets. I have already calculated expected results columns which works fine. 

What I am trying to achieve is If (placement  date = current month, expected result, else targets)

Issue I am facing is both placement date and targets are in two different tables and I can't seem to reference either or. Not even via "RELATED" function

If anyone else has another idea to value in, by all means. 

Appreciate the assistance

[v-yuta-msft · Accepted solution]

@Anonymous ,

So based on your description, placement date and targets are in dimensional tables, right? You can achieve the reference measure using related() but it should be included in a filter() function like pattern below:

Measure =
VAR placement_date =
    CALCULATE (
        MAX ( Fact_Table[placement date] ),
        FILTER (
            Fact_Table,
            Fact_Table[placement date] = RELATED ( Dimensional_Table[placement date] )
        )
    )
RETURN
    IF ( placement_date = MONTH ( TODAY () ), [expected result], [targets] )

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi Jimmy. Thanks for the reply. 

Just wanted to confirm, does "Fact_Table" would come in twice after "FILTER" function? 

[v-yuta-msft]

@Anonymous ,

Table name should be the first argument, the syntax for filter should be FILTER(<table>,<filter>).

Community Support Team _ Jimmy Tao

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

[Anonymous]

Thank you so much for your help 🙂