Forum Discussion
Problem with the MEDIAN()
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:
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 !
hi Anonymous
MEDIAN
Syntax
DAXMEDIAN(<column>)
https://docs.microsoft.com/en-us/dax/median-function-dax
MEDIANX
Syntax
DAXMEDIANX(<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://blog.enterprisedna.co/how-to-calculate-the-median-value-in-power-bi-using-dax/
Regards,
Lin
3 Replies
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Hi amitchandak ,
Seems complex, can you please explain a little more ?
Thanks !hi Anonymous
MEDIAN
Syntax
DAXMEDIAN(<column>)
https://docs.microsoft.com/en-us/dax/median-function-dax
MEDIANX
Syntax
DAXMEDIANX(<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://blog.enterprisedna.co/how-to-calculate-the-median-value-in-power-bi-using-dax/
Regards,
Lin
