Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: DAX measure using values across tables...

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,667
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DAX measure using values across tables...


    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. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,843
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default 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] ) )
    )

    Last edited by VBA Geek; Nov 7th, 2018 at 01:21 PM.
    Excel and BI blog: http://xcelanz.com/

  3. #3
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,667
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Matty; Nov 8th, 2018 at 10:03 AM.

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,843
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    Excel and BI blog: http://xcelanz.com/

  5. #5
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,667
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Matty; Nov 9th, 2018 at 07:13 AM.

  6. #6
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,843
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    Excel and BI blog: http://xcelanz.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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