Creating a visual combing 3 different tables with similar data

[ank1997]

Hi all, 

I have three tables that I want to combine and be able to create a visual on a map. How do I show all of the states with data? For some reason it is only pulling the location from a single table...

Award	School	State
1	E	Y
2	F	Y
3	G	X

Grant	State
A	Y
B	Z

School	State
H	X
I	Y

[v-yingjl]

Hi @ank1997 ,

If you want to show all data about states on the map, ideally you can create a calculated table like this to extract the state column as a single table and create relationships based on the state between these tables:

State table = 
DISTINCT(
    UNION(
        DISTINCT('Table1'[State]),
        DISTINCT('Table2'[State]),
        DISTINCT('Table3'[State])
    )
)

By the way, notice that there are also School columns on table1 and table3 so you need to consider it first because the final table would be like this:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Ideally, you should have a table with a column of all of the unique states, or more likely schools, and that column should have a one to many relationship with your fact table. That way, you are able to display and all states by filtering on the unique column.

--Nate