Forum Discussion
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
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
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.
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.
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.
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
EVALUATEADDCOLUMNS (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.
