Forum Discussion

Patv's avatar
Patv
Helper II
4 years ago
Solved

Dense_Rank function in Power BI query (DAX)

Hello, 

I have a below query in oracle db and I want to replicate dense_rank functionality in power bi that returns same result in oracle but I don't know DAX much and still learning so posting question here.  I want to create a measure in Power BI that is exaclty same as below hilighted oracle query.  Attached is oracle sql and result of query for reference.

Could some one please help me here? Thank you in advance and appreciate your help!.

 

 

select
a12.YEAR_ID YEAR_ID,
a12.MONTH_ID MONTH_ID,
a13.MONTH_NAME MONTH_DESC,
(Max(a11.ENH_ACTIVE_COUNT) Keep (dense_rank Last order by a11.DATE_ID asc) + Max(a11.HELP_ACTIVE_COUNT) Keep (dense_rank Last order by a11.DATE_ID asc)) Total,
Max(a11.ENH_ACTIVE_COUNT) Keep (dense_rank Last order by a11.DATE_ID asc) Enhanced_Count,
Max(a11.HELP_ACTIVE_COUNT) Keep (dense_rank Last order by a11.DATE_ID asc) Help_Count
from PSR_FACTS a11
join RPT_PMATT_DEV.TIME_DIMENSION_DAY a12
on (a11.DATE_ID = a12.DATE_ID)
join TIME_DIMENSION_MONTH a13
on (a12.MONTH_ID = a13.MONTH_ID)
join TIME_DIMENSION_YEAR a14
on (a12.YEAR_ID = a14.YEAR_ID)
where a12.YEAR_ID in (2021,2022)
group by a12.MONTH_ID,
a13.MONTH_NAME,
a12.YEAR_ID,
a14.YEAR_DESC

Thank you, 

Vpat

  • Patv's avatar
    Patv
    4 years ago

    Thank you Grag for putting greate link about RANKX. I have achieved similar result  by using Calculate  function in DAX. 

     

    e.g. 

    VAR_ENHA_ACTIVE_COUNT = calculate(sum(PSR_FACTS[ENH_ACTIVE_COUNT]),TIME_DIMENSION_DAY[IS_LAST_DAY_OF_MONTH] = "Y" )+calculate(sum(PSR_FACTS[ENH_ACTIVE_COUNT]),PSR_FACTS[DAY_DATE] = today(),TIME_DIMENSION_DAY[IS_LAST_DAY_OF_MONTH] = "N")

     

4 Replies