cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Patv
Helper II
Helper II

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

pic1.png

Thank you, 

Vpat

1 ACCEPTED SOLUTION

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")

 

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@Patv Not an expert on Oracle and sql syntax but the RANKX function in DAX provides a Dense option. RANKX function (DAX) - DAX | Microsoft Docs


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Thank you Greg. I tried to use RANKX function in Power Bi but since I am not expert in DAX, I was not able to figure it out hence I posted a question.  Let's see if someone is able to help here. Thank you for your reply.

@Patv This might help you figure out RANKX: To *Bleep* with RANKX! - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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")

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!