New Column in Table based on TWO other tables - IF Statement

[lukeh1995]

Hi all,

I have a model with multiple tables containing salary information from staff, with the $ amounts coming from two seperate tables. I am wanting to create a NEW COLUMN in my fact table that pulls the data from one table IF the unique position number is in that table, otherwise from another table that contains the unique position number. Is this possible?

[danextian]

Hi @lukeh1995 ,

Try the following approaches:

If there is an existing relationship between the two tables

= RELATED ( 'Table1',[Column] )

If there isn't

= LOOKUPVALUE ( 'Table1'[Position], 'Table1'[Desired Column],  'Fact'[Position] )

=
CALCULATE (
    MAX ( 'Table1'[Desired Column] ),
    FILTER ( 'Table1', 'Table1'[Position] = EARLIER ( 'Fact'[Position] ) )
)

These columns are to be created in fact.

If none of these works, please provide context to your post by providing a sample data.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/144...

[lukeh1995]

Hi!

Thank you so much for your response.

I understand 'related' would work for two tables, but I have three: I am wanting to fill the values in the fact table from one dimension table if the primary key exists in it, else another table.

[v-rzhou-msft · Accepted solution]

Hi @lukeh1995 ,

I suggest you to try code as below to create a calculated column.

Column = 
VAR _Position1 =
    VALUES ( Dimtable1[Position] )
VAR _Position2 =
    VALUES ( Dimtable2[Position] )
VAR _CurPosition = 'Fact Table'[Position]
RETURN
    SWITCH (
        TRUE (),
        _CurPosition IN _Position1,
            CALCULATE (
                SUM ( Dimtable1[Amounts] ),
                FILTER ( Dimtable1, Dimtable1[Position] = EARLIER ( 'Fact Table'[Position] ) )
            ),
        _CurPosition IN _Position2,
            CALCULATE (
                SUM ( Dimtable2[Amounts] ),
                FILTER ( Dimtable2, Dimtable2[Position] = EARLIER ( 'Fact Table'[Position] ) )
            )
    )

Result is as below.

Best Regards,
Rico Zhou

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