ABS Function not accepting column name

[Anonymous]

Hi, 

Can somebody please help me in solving this problem statement ?

SQL : ((ABS(POSTPRICE-PRIORPRICE))/PRIORPRICE)*100
POSTPRICE and PRIORPRICE are the column names in my table

I tried in DAX using the ABS Function that is available in Power BI, but this error is coming.
Error : Failed to resolve 'POSTPRICE'. It is not a valid table, variable or function name.


Thanks !

[v-frfei-msft]

Hi @Anonymous ,

I think you want to create a measure. In your scenario, we should add MIN or MAX before your column name in your formula like that.

((ABS(max(POSTPRICE)-max(PRIORPRICE)))/max(PRIORPRICE)*100

If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hi @v-frfei-msft , 

MAX doesn't serve my purpose as I don't want the maximum value from my column. This won't works for me.
Need to implement something else.

[rajulshah]

@Anonymous, If you share sample data of maybe 20-25 rows, would be able to help in a better way.

[rajulshah]

Hello @Anonymous,

Can you please tell exactly what is your requirement? Also, are you using this DAX in a calculated measure?

Because calculated won't just have column names without any aggregation in their calculation.
 

[Anonymous]

How to implement this SQL Statement in Power BI ?
I am trying to create a measure for this because with this query , I will be getting a numerical figure that I will have to populate in a card on power bi dashboard. 

SQL:
ROUND(MEDIAN(CASE WHEN CHALLENGERESULT = 'ABC' AND PRIORPRICE <> 0 AND CREATEDTIMESTAMP BETWEEN TRUNC(TO_DATE($P{DATE_STR},'DD-MON-YYYY'))- 8 AND TRUNC(TO_DATE($P{DATE_STR},'DD-MON-YYYY')) THEN ((ABS(POSTPRICE-PRIORPRICE))/PRIORPRICE)*100 ELSE NULL END),2) - ROUND(MEDIAN(CASE WHEN CHALLENGERESULT = 'ABC' AND PRIORPRICE <> 0 AND CREATEDTIMESTAMP BETWEEN TRUNC(TO_DATE($P{DATE_STR},'DD-MON-YYYY'))- 15  AND  TRUNC(TO_DATE($P{DATE_STR},'DD-MON-YYYY'))- 8  THEN ((ABS(POSTPRICE-PRIORPRICE))/PRIORPRICE)*100 ELSE NULL END),2) "W Magnitude Trend"

CREATEDTIMESTAMP : Date Time column in my table
CHALLENGERESULT : Column in my table and i have 3 types of values in this column ('ABC', 'DEF', 'OTHERS')
PRIORPRICE : Column in my table that contains some numerical figure
POSTPRICE : Column in my table that contains some numerical figure
In the $P{DATE_STR}, user is entering the date


I think now the requirement is clear, if you still need any type of clarification then please message me.
Thanks !

[rajulshah]

@Anonymous, please try creating calculated column and apply the same DAX query as you used earlier.

[Anonymous]

Hi @rajulshah ,

I made my requirement in the calculated column but unable to use that column in the measure.
This doesn't works.