Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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-...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak ,

Seems complex, can you please explain a little more ?

Thanks !

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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