Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Dicken
Responsive Resident
Responsive Resident

DAX index Function how to use?

Hi, I have not used index in dax pbi, but thouhgt I'd have a look, so created a date table ; 

 

dDate = 
 ADDCOLUMNS( 
 CALENDAR( DATE( 2020,1,1), DATE( 2025,12,31) ) , 
 "Year", YEAR([Date]), 
 "Month", FORMAT([Date],"MMM") , 
 "ms", MONTH([Date]), 
  "FiscalQ",  CEILING( MONTH( EDATE([Date],-3)),3) /3
 )

 

and then decided to use index to bring back the top row,  so in excel Index( Table, 1, 0 )  , 

Table = INDEX(1 , dDate, ORDERBY(dDate[Date]) )

 

but i get a message saying it may have duplicate rows, well it's a calendar so I don't think so,   
Richard. 



2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Dicken , I created a table with the same code, and it worked

 

amitchandak_0-1720802939678.png

 

In case of measure use

Maxx( INDEX(1 , dDate, ORDERBY(dDate[Date]) ), [Date])

 

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

@Dicken Try this : 

 use distinct around  the table you are using in window functions 

 

let me know if this works 

View solution in original post

6 REPLIES 6
Dicken
Responsive Resident
Responsive Resident

no, 

 CALENDAR(DATE(2020,1,1), DATE(2020,12,31)  ) ,
Then new table 

  Table 2 = DISTINCT(WINDOW(1,ABS,1,ABS ,'Table') ) 
Erorr message 
  WINDOW's Relation parameter may have duplicate rows. This is not allowed. 

But it will acceept 

Table = WINDOW(1,ABS,1,ABS,
 CALENDAR(DATE(2020,1,1), DATE(2020,12,31)  ) ) 

Richard. 

amitchandak
Super User
Super User

@Dicken , I created a table with the same code, and it worked

 

amitchandak_0-1720802939678.png

 

In case of measure use

Maxx( INDEX(1 , dDate, ORDERBY(dDate[Date]) ), [Date])

 

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Sorry, just to add did this in power pivot Dax studo, but this is what I would expect index to return, 
a one row  ( top ) table; 

EVALUATE 
 FILTER( 'Calendar', 
  'Calendar'[Date] = DATE( 2020,1,1) 
  )

Dicken_0-1720806785228.png

Richard. 

So does it return a table, as that's what I was using; 

New Table,  

Table = INDEX(1, dDate, ORDERBY(dDate[Date],ASC

out of interest I get the same message for window.   

Table 2 = WINDOW( 1,ABS,1,ABS,dDate )  again I get duplicate error message, 

so still none the wiser as to how it works,   
i thought I'd try a simpler date table ; 
dDate = ADDCOLUMNS(

 CALENDAR( DATE( 2020,1,1), DATE(2020,12,31) ),
 "Year", YEAR([Date]),
 "Month", FORMAT([Date],"MMM")
and put this into studio,  but get message,  an item with the saem key has already been added? 
Don't have these problems in power pivot. 

Richard 

@Dicken Try this : 

 use distinct around  the table you are using in window functions 

 

let me know if this works 

Thanks, 

RD

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors