DAX Calculation from related table

[Anonymous]

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

[Anonymous]

@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]

 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]

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!

[Anonymous]

Thanks @Anonymous 🙂

But i was not referring to the formatting itself, rather more the logic to ensure it is working efficiently

[Anonymous · Accepted solution]

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.

[danno]

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

[Anonymous]

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

Thanks @danno