Forum Discussion

BartVlek's avatar
BartVlek
Helper II
6 years ago

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...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

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's avatar
BartVlek
Helper II
6 years ago

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's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      BartVlek
      Helper II
      6 years ago

      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