cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StefanDK Frequent Visitor
Frequent Visitor

Building a new table using variant specific ratings

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Building a new table using variant specific ratings

@StefanDK,

 

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]
        )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: Building a new table using variant specific ratings

@StefanDK,

 

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]
        )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.