cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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 =
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
Super User

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

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

Super User

@Dicken Try this :

use distinct around  the table you are using in window functions

let me know if this works

6 REPLIES 6
Helper III

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.

Super User

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

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

Helper III

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

Richard.

Helper III

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 ;

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
Super User

@Dicken Try this :

use distinct around  the table you are using in window functions

let me know if this works

Helper III

Thanks,

RD

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors