Building a new table using variant specific ratings

Question

Hello,&nbsp;I have data scattered around&nbsp;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.&nbsp;Simplified I have three tables like this:- one table containing the master dataMasterScoresitemscoreA1B5C4D6E2F3G4&nbsp;- one table containing variant specific data:VariantScoresvariantitemnew scoreVAR1A2VAR1F2VAR1G3VAR2B4VAR2D5VAR3C3VAR3D7VAR3E3&nbsp;- and one table telling me which item/row to exclude from each variant:ExcludedFromVariant&nbsp;variantitemVAR1BVAR1CVAR2AVAR3AVAR3F&nbsp;I would now&nbsp;like to make one new table out of&nbsp;it that looks like:NewTablevariantitemscoreMASTERA1MASTERB5MASTERC4MASTERD6MASTERE2MASTERF3MASTERG4VAR1A2VAR1D6VAR1E2VAR1F2VAR1G3VAR2B4VAR2C4VAR2D5VAR2E2VAR2F3VAR2G4VAR3B5VAR3C3VAR3D7VAR3E3VAR3G4&nbsp;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 Answer

Anonymous,
&nbsp;
You may refer to the DAX below that adds a calculated table&nbsp;
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]
        )
    )

Forum Discussion

Building a new table using variant specific ratings

1 Reply

Related content

Build 2.151.1182.0 Error

Specific matrix

Build - Data field weird behavior

Saving Notebooks to specific folder

Build Availability Table

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

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