cancel
Showing results for
Did you mean:
Highlighted
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
Community Support Team

## Re: Building a new table using variant specific ratings

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
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.
Community Support Team

## Re: Building a new table using variant specific ratings

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
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.