cancel
Showing results 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

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

12 REPLIES 12
Community Support

Hi, @BartVlek

total:

discount:

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:

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

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

Result:

Best Regards

Allan

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

Helper II

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
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.
Community Support

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
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.

Helper II

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

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.

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

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.

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

Helper II

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

Super User

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

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

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.

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

Helper II

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

Super User

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

Helper II

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

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

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors