Thanks:  0
Likes:  0

# Thread: DAX measure using values across tables...

1. ## DAX measure using values across tables...

Hi,

Looking for some help!

Data set up as follows...

Table 1 (sales data)

 ID Product DC Month Sales Apple1 Apple 1 1 1500 Pear1 Pear 1 1 50 Orange1 Orange 1 1 30 Apple1 Apple 1 2 1200 Pear1 Pear 1 2 25 Orange1 Orange 1 2 60

Table 2 (conversion data)

 ID Product DC Denominator Numerator Apple1 Apple 1 1 15 Pear1 Pear 1 2 5 Orange1 Orange 1 1 2 Apple2 Apple 2 1 30 Pear2 Pear 2 2 10 Orange2 Orange 2 1 4

There is a relationship between Table 1 and Table 2 via the ID column (a concatenation of 'Product' and 'DC').

I want a measure to convert the sales data in Table 1 using the relevant denominator and numerator in Table 2. In simple terms, the formula would be:

Code:
`=SUM(Table1[Sales]) * Table2[Denominator] / Table2[Numerator]`
The problem I can't get my head around is how to get the formula to use the correct row from Table 2.

The resulting measure should return the values as shown in 'Sales Revised' below:

 Product Month Sales Revised Apple 1 100 Pear 1 20 Orange 1 15 Apple 2 80 Pear 2 10 Orange 2 30

Hope someone can help.

Cheers,

Matty

2. ## Re: DAX measure using values across tables...

You can create a primary key during the ETL Process such as:

Then you can do something like ( numerator and denominator are mixed up by the way )

Code:
```Measure =
IF(
HASONEVALUE( Conversions[PrimaryKey] ),
SUM( Sales[Sales] ) * DIVIDE( SELECTEDVALUE( Conversions[Denominator] ), SELECTEDVALUE( Conversions[Numerator] ) )
)```

3. ## Re: DAX measure using values across tables...

Hi VBA Geek,

Thanks for your help.

I'd actually come up with the following, which also works:

Code:
```SUMX (
Table1,
CALCULATE (
SUM ( Table1[Sales] ) * MIN ( Table2[Denominator] )
/ MIN ( Table2[Numerator] )
)
)```
Although I'd imagine yours is less *expensive*, processing-wise?

Also, as I'm using Excel DAX, SELECTEDVALUE isn't available in there, hence the use of MIN instead, though I don't know if this could have unexpected consequences in certain circumstances.

Obviously, as we can't have multiple joins in Excel (or Power BI), the ID column was necessary to create the one-to-many relationship, so I'm not sure I understand how your Primary Key and Foreign Key concept is going to work here. Could you explain further?

Thanks again...

Matty

4. ## Re: DAX measure using values across tables...

Hi Matty

you should always make use of physical relationships whenever possible, it is true you can use DAX to 'create a relationship' on the fly but physical relationships are generally much faster.

In place of SELECTEDVALUE you can use VALUES in this case.

I just created an unique ID per combination of Product and DC instead of concatenating these columns into a new column in order to create the relationship since relationships working on integers are faster

5. ## Re: DAX measure using values across tables...

Hi VBA Geek,

Thanks for your suggestion regarding VALUES.

Could you share your method for creating the unique ID in Power Query, please? Interested to understand how you have done it.

Thanks,

Matty

6. ## Re: DAX measure using values across tables...

You conversion data table has unique rows so you just need to add an index column which serves are PrimaryKey

Code:
```let    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEk1VNKBMIC0IQybKsXqRCsFpCYWgbggGipjBMQQSf+ixLx0sG4IC0m7EVgB2FAjJNONoNLGBnDTjRCmG8FUGCAZb4RsPEy/iVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t, DC = _t, Denominator = _t, Numerator = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Product", type text}, {"DC", Int64.Type}, {"Denominator", Int64.Type}, {"Numerator", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "PrimaryKey", 1, 1)
in
#"Added Index"```

The other query, once you have loaded also the Sales Table data is just a matter of join:

Code:
```let
Source = Table.NestedJoin(#"Sales Data",{"Product", "DC"},#"Conversion Data",{"Product", "DC"},"Conversion Data",JoinKind.Inner),
ExpandedConversionData = Table.ExpandTableColumn(Source, "Conversion Data", {"PrimaryKey"}, {"ForeignKey"}),
RemovedOtherColumns = Table.SelectColumns(ExpandedConversionData,{"ForeignKey", "Month", "Sales"})
in
RemovedOtherColumns```

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•