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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BartVlek
Helper II
Helper II

Problems with syntax in combination with Related

I am having problems with getting the syntax right for the following expression:

 

CostPrice =
IF (
(RELATED ( 'discount'[percentage] ) = 0,
( 'total'[PAMP] ),
( ( 'total'[AMOUNT] ) * ( RELATED ( 'discount'[percentage] ) )
)))

 

My DAX Studio keeps telling me the syntax is incorrect, and I tired about everything I could. Please help. Thank you !

12 REPLIES 12
v-alq-msft
Community Support
Community Support

Hi, @BartVlek 

 

Based on your description, I created data to reproduce your scenario.

total:

b6.png

 

discount:

b2.png

 

b3.png

 

You may create a calculated column as below in Power BI Desktop.

 

CostPrice = 
IF(
    RELATED(discount[percentage]) = 0,
    total[PAMP],
    total[Amount]*RELATED(discount[percentage])
)

 

 

Result:

b4.png

 

If you want to display the result with a new column in Dax Studio. You may try the formula as below.

 

EVALUATE
ADDCOLUMNS(
    total,
    "CostPrice2",
    IF(
	    RELATED(discount[percentage]) = 0,
	    total[PAMP],
	    total[Amount]*RELATED(discount[percentage])
    )
)

 

 

Result:

b5.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

Thank you very much. I have the feeling I am almost there. This is how I used it (translated into Dutch to the actual situation

 

EVALUATE
ADDCOLUMNS (
totaal,
"KOSTPRIJS-CORR", IF (
RELATED ( korting[kortingspercentage] ) = 0,
totaal[PAMP],
totaal[bedrag] * RELATED ( korting[kortingspercentage] )
)
)
 
And when applied into PBI this is the error mesage I get:
To use special characters in a column name, enclose the entire name in brackets ( [] ) and add a ] to any closing brackets in the name.

Hi, @BartVlek 

 

In Power BI Desktop, I need to create a calculated column as below.

CostPrice = 
IF(
    RELATED(discount[percentage]) = 0,
    total[PAMP],
    total[Amount]*RELATED(discount[percentage])
)

 

While in Dax Studio, you need to use the following formula.

EVALUATE
ADDCOLUMNS(
    total,
    "CostPrice2",
    IF(
	    RELATED(discount[percentage]) = 0,
	    total[PAMP],
	    total[Amount]*RELATED(discount[percentage])
    )
)

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks again. When I run the transformed script into PBI I get the following error-message:

 

The syntax for 'totaal' is incorrect. (DAX(IF( RELATED(korting[kortingspercentage]) = 0. totaal[PAMP], totaal[bedrag]*RELATED(korting[kortingspercentage])))).

 

This is the script I used:

KOSTPRIJS-CORR =
IF(
RELATED(korting[kortingspercentage]) = 0,
totaal[PAMP],
totaal[bedrag]*RELATED(korting[kortingspercentage])
)

 

Thanks again,

Bart

 

AllisonKennedy
Super User
Super User

What does the relationship look like? I suspect it has to do with cross filter direction. 

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thta is interesting. So it can matter from which table the relationship is created?

@BartVlek  Not really from which table the relationship is created, but the cross filter direction.

 

To simplify things, if the arrow does not point to the total table, you will not be able to pull discount columns into a calculated column in total table using RELATED. If the chain of relationships goes through another table, the arrows always have to point to the total table. 

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

I am certain the relationship is created correctly.

The syntax error I keep receiving is: Syntax error: expected IN  this is after the final bracket

@BartVlek , Share a screenshot of the error and final formula.  If possible share pbix, after removing sensitive information.

I am not unwilling to that, but my PBI dashboard is all in Dutch ... I just translated for your understanding, but the syntax remains the same. How can I share a screenshot? Sorry very new to this

 

 

amitchandak
Super User
Super User

@BartVlek , Are these two tables joined? Are you creating a measure or a column?

Can you share sample data and sample output.

 

If it a column this should work

CostPrice =
IF (RELATED ( 'discount'[percentage] ) = 0, 'total'[PAMP] , 'total'[AMOUNT] * RELATED ( 'discount'[percentage] )
)

If measure =

CostPrice =
Sumx('total', IF (RELATED ( 'discount'[percentage] ) = 0, 'total'[PAMP] , 'total'[AMOUNT] * RELATED ( 'discount'[percentage] )
))

Yes the tables are joined, and I am creating a column

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors