DAX to create new table from 3 other tables and check which table is the source

[JasonYous]

Hello,

In my model I have 3 tables, all identical ,I'm trying to UNION to grab one column from each table to create a master list, then a column to display1 or 0 based on which table is the source table, so I can use that in slicers

Table 1:

ServerName

Server 1

Server 2

Table 2: ServerName

ServerName

Server 1

Server 3

Server 4

Table 3: ServerName

ServerName

Server 5

Server 6

New Table (Master Server List) :

ServerName, Table 1, Table 2, Table 3, Source Table

Server 1, 1,1,0, Table 1&Table 2

Server 2, 1,0,0, Table 1

Server 3, 0,1,0, Table 2

Server 4, 0,1,0, Table 2

Server 5, 0,0,1,Table 3

Server 6, 0,0,1,Table 3

Master Server List = UNION( SELECTCOLUMNS(Table1,"ServerName", [ServerName]),

                            SELECTCOLUMNS(Table2,"ServerName", [ServerName]),

                            SELECTCOLUMNS(Table3,"ServerName", [ServerName])

                            )

I'm stuck at creating the other columns and needs guidence please

thank you

[johnt75]

Try

Master table =
UNION (
    GENERATE (
        DISTINCT ( 'Table1'[Server name] ),
        ROW ( "Table 1", 1, "Table 2", 0, "Table 3", 0, "Source Table", "Table 1" )
    ),
    GENERATE (
        DISTINCT ( 'Table2'[Server name] ),
        ROW ( "Table 1", 0, "Table 2", 1, "Table 3", 0, "Source Table", "Table 2" )
    ),
    GENERATE (
        DISTINCT ( 'Table3'[Server name] ),
        ROW ( "Table 1", 0, "Table 2", 0, "Table 3", 1, "Source Table", "Table 3" )
    )
)

[JasonYous]

Thank you, that worked if the server is only in 1 Table, however the server can be in multiple tables, Ex: Server12 can be in Table1 & Table2 for example 

[johnt75]

Ah, OK. Try

Master table =
VAR SummaryTable =
    UNION (
        GENERATE (
            DISTINCT ( 'Table1'[Server name] ),
            ROW ( "Table 1", 1, "Table 2", 0, "Table 3", 0, "Source Table", "Table 1" )
        ),
        GENERATE (
            DISTINCT ( 'Table2'[Server name] ),
            ROW ( "Table 1", 0, "Table 2", 1, "Table 3", 0, "Source Table", "Table 2" )
        ),
        GENERATE (
            DISTINCT ( 'Table3'[Server name] ),
            ROW ( "Table 1", 0, "Table 2", 0, "Table 3", 1, "Source Table", "Table 3" )
        )
    )
VAR Result =
    ADDCOLUMNS (
        SUMMARIZE ( SummaryTable, [Server name] ),
        "Table 1", CALCULATE ( SUMX ( SummaryTable, [Table 1] ) ),
        "Table 2", CALCULATE ( SUMX ( SummaryTable, [Table 2] ) ),
        "Table 3", CALCULATE ( SUMX ( SummaryTable, [Table 3] ) ),
        "Source Table", CALCULATE ( CONCATENATEX ( SummaryTable, [Source table], " & " ) )
    )
RETURN
    Result

[JasonYous]

Thank you! it took forever to run and looks like it's looping to forever, the results are totally wrong though