Building a new table using variant specific ratings

[Anonymous]

Hello,

I have data scattered around in tables and would like to make one table out of it so that I can use that one for further calculations, filtering and visualisation. Simplified I have three tables like this:

- one table containing the master data

MasterScores
item	score
A	1
B	5
C	4
D	6
E	2
F	3
G	4

- one table containing variant specific data:

VariantScores
variant	item	new score
VAR1	A	2
VAR1	F	2
VAR1	G	3
VAR2	B	4
VAR2	D	5
VAR3	C	3
VAR3	D	7
VAR3	E	3

- and one table telling me which item/row to exclude from each variant:

ExcludedFromVariant
variant	item
VAR1	B
VAR1	C
VAR2	A
VAR3	A
VAR3	F

I would now like to make one new table out of it that looks like:

NewTable
variant	item	score
MASTER	A	1
MASTER	B	5
MASTER	C	4
MASTER	D	6
MASTER	E	2
MASTER	F	3
MASTER	G	4
VAR1	A	2
VAR1	D	6
VAR1	E	2
VAR1	F	2
VAR1	G	3
VAR2	B	4
VAR2	C	4
VAR2	D	5
VAR2	E	2
VAR2	F	3
VAR2	G	4
VAR3	B	5
VAR3	C	3
VAR3	D	7
VAR3	E	3
VAR3	G	4

And to make it even more difficult: I don't know upfront how many variants I have.

Does anyone have an idea?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the DAX below that adds a calculated table 

Table =
VAR t =
    UNION (
        ROW ( "variant", "MASTER" ),
        DISTINCT (
            UNION (
                VALUES ( VariantScores[variant] ),
                VALUES ( ExcludedFromVariant[variant] )
            )
        )
    )
RETURN
    ADDCOLUMNS (
        GENERATEALL (
            t,
            FILTER (
                MasterScores,
                NOT CONTAINS (
                    ExcludedFromVariant,
                    ExcludedFromVariant[variant], [variant],
                    ExcludedFromVariant[item], MasterScores[item]
                )
            )
        ),
        "new score", LOOKUPVALUE (
            VariantScores[new score],
            VariantScores[variant], [variant],
            VariantScores[item], MasterScores[item]
        )
    )