Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Calculation from related table

Hi all,

 

I have this DAX expression and it works fine.

 

PTD Sales =
CALCULATE (
SUM ( Transactions[Sales] ),
FILTER (
Transactions,
Transactions[period_year] = MAX ( Transactions[period_year] )
&& Transactions[date] <= MAX ( Transactions[date] )
)
)
 
Now i have created a seperate Date table and seems like I cannot have the same expression but using the fields from the new data table:
 
PTD Sales =
CALCULATE (
SUM ( Transactions[Sales] ),
FILTER (
Transactions,
Date[period_year] = MAX ( Date[period_year] )
&& Date[date] <= MAX ( Date[date] )
)
 
Can anyone help rewrite the DAX expression to work with the date dimension?
 
Thanks
 
 
 
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    I can't tell you the best way to write your particular DAX expression. However, since you are interested in optimizing your DAX, sqlbi.com always publish articles about the best way to optimize DAX.

     

    Here is a link to their most recent articles on DAX optimization: 

     

    https://www.sqlbi.com/articles/?author=&tag_id=411

     

    Also, they have DAX studio which allows you to test how long your DAX takes to run. You can change things up to see if your DAX changes affect performance.

     

    Hope that helps.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    can you just confirm the structure of your model - is the transaction date connected to the date dimension via a date key? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yes the Transactions table is connected to the DateKey, so relationships are established already.

       

      Thanks Anonymous

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous

     

    I'm looking at your 2nd expression. It looks like you are filtering the transaction table, but then you don't really specify how to filter the transaction table; your parameters are for a different table.

     

    Typically, if I'm trying to filter a specific table based on parameters in another table (related, inactive/active, fact side/dimension side), I use RELATED, RELATEDTABLE, or USERELATIONSHIP in my filter statement.

     

     

    Here is an examle from my sqlbi slides...

     

    SUMX (
    FILTER (
    ProductCategory,
    COUNTROWS ( RELATEDTABLE ( Product ) ) > 10
    ),
    SUMX ( RELATEDTABLE ( Sales ), Sales[SalesAmount] )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       I have written this expression and seems to be working fine... Not sure if this can be optimised in any way or written using best practises?

       

      PTD Sales =
      CALCULATE (
      SUM ( Transactions[Sales] ),
      FILTER (
      Transactions,
      RELATED('Date'[Period Year]) = Max( 'Date'[Period Year] )
      && RELATED('Date'[date]) <= Max( 'Date'[date] )
      )
      )
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Great question. 

         

        If you are formatting your DAX properly, which you are obviously doing based on your latest post, you are following best practices with respect to writting/formatting DAX.

         

        Keeping up with best practices and your workload can be difficult. The staff at SQLBI do a lot of work to educate people regarding best practices; there is also great content on this board.

         

        If you haven't already, check out sqlbi.com. Marco is in some vids with the doods that create Guy In A Cube YouTube content.

         

        Well done!