Problems with syntax in combination with Related

[BartVlek]

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 !

[v-alq-msft]

Hi, @BartVlek 

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

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

[BartVlek]

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.

[v-alq-msft]

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.

[BartVlek]

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]

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

[BartVlek]

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

[AllisonKennedy]

@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

[BartVlek]

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

[amitchandak]

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

[BartVlek]

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]

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

[BartVlek]

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