Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
michael_ma
Frequent Visitor

Translate tableau expression to Dax

Hi Everyone, 

 

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

 

Tableau expression : 

michael_ma_0-1701216917731.png

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

1 ACCEPTED SOLUTION
123abc
Resident Rockstar
Resident Rockstar

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.

View solution in original post

3 REPLIES 3
123abc
Resident Rockstar
Resident Rockstar

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.

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

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors