DAX measure using values across tables...

[Matty]

Hi,

Looking for some help!

Data set up as follows...

Table 1 (sales data)

[TABLE="width: 338"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Product[/TD]
[TD]DC[/TD]
[TD]Month[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]Pear1[/TD]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Orange1[/TD]
[TD]Orange[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD]Pear1[/TD]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]Orange1[/TD]
[TD]Orange[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 (conversion data)

[TABLE="width: 400"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Product[/TD]
[TD]DC[/TD]
[TD]Denominator[/TD]
[TD]Numerator[/TD]
[/TR]
[TR]
[TD]Apple1[/TD]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Pear1[/TD]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Orange1[/TD]
[TD]Orange[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Apple2[/TD]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Pear2[/TD]
[TD]Pear[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orange2[/TD]
[TD]Orange[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

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:

=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:

[TABLE="width: 250"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Month[/TD]
[TD]Sales Revised[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Hope someone can help.

Cheers,

Matty

 

[VBA Geek]

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 )

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

 

[Matty]

Hi VBA Geek,

Thanks for your help.

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

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

 

[VBA Geek]

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

 

[Matty]

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

 

[VBA Geek]

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

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:

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