Get first non-null value from two tables with relationship for a filter

[Anonymous]

I have two tables with a many to one relationship:

Table1

Col1
1
2

3

Table2

Col1
1

1

1

5

5

4

I want to create a column that I can filter on that will show values:

1

2

3

4

5

I tried creating a column:

Col3 = IF( ISBLANK('Table1'Col1), MAX('Table2'Col1),'Table1'Col1)

But htat doesn't seem to be working.

Any help appreciated 🙂 

[v-yuezhe-msft · Accepted solution]

@Anonymous,

You can create a new table using Dax below.

newtable = 
FILTER(
DISTINCT(
UNION(
DISTINCT(Table1[Col1]),
DISTINCT(Table2[Col1])
)
),
NOT(ISBLANK(Table1[Col1]))
)

)

Regards,
Lydia

[MFelix]

Hi @Anonymous,

Not sure what you want to do but if you want to create a new table only with unique values the best way to do it is on the query editor.

You should append both tables, delete all the columns you don't need and then Remove duplicates.

Regards.

MFelix