Problem with the MEDIAN()

[Anonymous]

Hi, 

I am creating the equivalent of the SQL Query in POWER BI
SQL QUERY:

ROUND(MEDIAN(CASE WHEN CHALLENGERESULT = 'ABC' AND PRIORPRICE <> 0 AND CREATEDTIMESTAMP BETWEEN TRUNC(TO_DATE('26-JAN-2019','DD-MON-YYYY'))-8
AND TRUNC(TO_DATE('26-JAN-2019','DD-MON-YYYY')) THEN ((ABS(POSTPRICE-PRIORPRICE))/PRIORPRICE)*100 ELSE NULL END),2) "W % Adjustment Magnitude"

DAX:

ROUND(MEDIAN(CALCULATE(COUNTROWS(TABLE),FILTER(TABLE,TABLE[CHALLENGERESULT] = "ABC" && TABLE[PRIORPRICE]<> 0 && TABLE[CREATEDTIMESTAMP].[Date] >= ( SELECTEDVALUE('DATE TABLE'[Date] ) - W_NO_DAYS ) && TABLE[CREATEDTIMESTAMP].[Date] < (SELECTEDVALUE('DATE TABLE'[Date]) - W_NO_DAYS ) + W_NO_DAYS ))),2)

But getting the error "The MEDIAN function only accepts a column reference as the arguement number 1 "
How to solve this issue ?
can somebody please help ?

Thanks !

[amitchandak]

Calculate already return summed up rows, another function might not work

Use medianx

https://docs.microsoft.com/en-us/dax/medianx-function-dax

Take out formula till calculating into a variable

medianx_1 =
Var _formula = <You formula till calculate >
return
medianx(table,_formula )

But you need to have some level in between

medianx_1 =
Var _formula = <You formula till calculate >
return
medianx(values(table[Level of countrows]),_formula )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak ,

Seems complex, can you please explain a little more ?

Thanks !

[v-lili6-msft · Accepted solution]

hi @Anonymous 

MEDIAN

Syntax

DAX

MEDIAN(<column>)

https://docs.microsoft.com/en-us/dax/median-function-dax

MEDIANX

Syntax

DAX

MEDIANX(<table>, <expression>)  

https://docs.microsoft.com/en-us/dax/medianx-function-dax

Since in your DAX formula, blue part is expression, so you need MEDIANX instead of MEDIAN

ROUND(MEDIAN(CALCULATE(COUNTROWS(TABLE),FILTER(TABLE,TABLE[CHALLENGERESULT] = "ABC" && TABLE[PRIORPRICE]<> 0 && TABLE[CREATEDTIMESTAMP].[Date] >= ( SELECTEDVALUE('DATE TABLE'[Date] ) - W_NO_DAYS ) && TABLE[CREATEDTIMESTAMP].[Date] < (SELECTEDVALUE('DATE TABLE'[Date]) - W_NO_DAYS ) + W_NO_DAYS ))),2)

https://community.powerbi.com/t5/Community-Blog/Iterating-Functions-SUMX-amp-AVERAGEX-Why-You-Need-To-Understand/ba-p/546347

https://blog.enterprisedna.co/how-to-calculate-the-median-value-in-power-bi-using-dax/

Regards,

Lin