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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Add a Calculated index column using DAX

Hello All,

 

How can we create a index calculated column or measure using DAX, like we do in power query.

 

 

Any help.

 

Mohan V

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

What does your data source look like? I create the following sample table and get expected result.

sample tablesample table
I create measure using the formula below.

Index = RANKX(ALL(Test),FIRSTNONBLANK(Test[Project ID],Test[Project ID]),,ASC,Dense)


Then create a table, select the Project ID and measure as the value level, you will get expected result as follows.

2.PNG

Best Regards,
Angelia

View solution in original post

9 REPLIES 9
spuder
Resolver IV
Resolver IV

Hi @Anonymous,

 

have you tried to use the RANKX() function. This calculates a ranking depending on a value. So you should be able to generate an index column with it.

 

Greetings

 

spuder

Anonymous
Not applicable

I tried @spuder... but could find the solution.

 

Any hint that how can i achieve it.

@Anonymouswhat is the sort order of your table and does it has duplicate values in the sorted columns?

Anonymous
Not applicable

@spuder it is ascending and no it dont have any duplicate values.

@Anonymous

 

then it should be working with RANKX()

 

visual.png

Anonymous
Not applicable

@spuder I just tried this.

 

But i got the output which i am not expecting.

Capture.PNG

I would like to get the no of rows that i will be having in that table dynamically,

like in that above image i should get as 1

                                                                2

                                                               3

                                                               4

                                                               5

Values.

Any help

Hi @Anonymous,

What does your data source look like? I create the following sample table and get expected result.

sample tablesample table
I create measure using the formula below.

Index = RANKX(ALL(Test),FIRSTNONBLANK(Test[Project ID],Test[Project ID]),,ASC,Dense)


Then create a table, select the Project ID and measure as the value level, you will get expected result as follows.

2.PNG

Best Regards,
Angelia

You can't rely on RANKX when adding Index as RANKX generates same index for the similar target column values.

OK. But as far as I can see. You don't want to index a source table but a table visual, right? And the table visual does not include all Project IDs, right? Then it won't work that way. Because you always rank against the source table with ALL Project IDs.

 

So I don't see a solution to get this problem working. Sorry. Smiley Sad

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.