Translate tableau expression to Dax

[michael_ma]

Hi Everyone, 

I need some help translating the following Tableau expression into Dax. 

Tableau expression : 

This is my current attempt for a Dax expression but I'm getting an error with power bi not recognizing product line column: 

CALCULATE (
IF (
MAX ( RELATEDTABLE('Products')[Product Line] ) = "Keys"
&& 'Transactions'[Invoice Gross Amount] = 0,
TRUE (),
FALSE ()
),
ALLEXCEPT ( 'Transactions', 'Transactions'[Document Number] )
)

[123abc · Accepted solution]

Your DAX expression seems close, but it looks like there might be an issue with how you're referencing the 'Products' table and the 'Product Line' column. In Power BI, you generally use relationships between tables to access columns from other tables. Here's a revised version of your DAX expression:

CALCULATE (
IF (
MAX ( 'Products'[Product Line] ) = "Keys"
&& 'Transactions'[Invoice Gross Amount] = 0,
TRUE (),
FALSE ()
),
ALLEXCEPT ( 'Transactions', 'Transactions'[Document Number] )
)

In this revision, I replaced RELATEDTABLE('Products') with direct reference to the 'Products' table, assuming there is a relationship between the 'Products' table and the 'Transactions' table. Ensure that there is a relationship established between the 'Products' table and the 'Transactions' table on the relevant columns.

If the issue persists, please check the column names and make sure they match the actual column names in your data model. Additionally, make sure that the relationships between tables are set up correctly.

If you're still encountering errors, providing more details about your data model and the relationships between tables would help in providing a more accurate solution.

[michael_ma]

Thank you for the reply. 

I was able to get the expression to work using:

CALCULATE (
IF (
MAX ( 'Products'[Product Line] ) = "Keys"
&& SUM ( 'Transactions'[Invoice Gross Amount]) = 0,
TRUE (),
FALSE ()
),
ALLEXCEPT ( 'Transactions', 'Transactions'[Document Number] )
)

I guess the piece I was missing was that you can't use the related function while using calculate

[123abc]

ou're correct! In DAX, when you're inside the CALCULATE function, the context for evaluation is already determined, and you typically use column references directly without the need for the RELATED or RELATEDTABLE functions.

Your modified expression looks good. The CALCULATE function changes the context for evaluation, and within its scope, you can directly refer to columns in the current table or use aggregation functions like SUM without the need for the RELATED functions.

If you have any more questions or if there's anything else I can help you with, feel free to ask!