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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wxqqaz
New Member

Add an index column to a summarised table

Hi guys

 

Is that possible to add an index column to a summarised table which is not directly derived from a query, or alternatively, can I simply create an individual column counting the row number for each row?

 

Cheers eg.PNG

5 REPLIES 5
DaveARoberts
Frequent Visitor

Add an index column with the formula

 

ID = RANKX('TableName',[RankColumn],[RankColumn],1)
where RankColumn is an existing column with unique numerical values

 

If no such RankColumn exists then create one...

 

RND = RAND()

ID = RANKX('TableName',[RND],[RND],1)
v-jiascu-msft
Employee
Employee

Hi @wxqqaz,

 

Can you add an index in the table "YTDDATA"? If you can , there may be a workaround.

About adding an index in the summarized table:

Daily report table =
SUMMARIZE ( ytddata, ... ... 
                        "OldIndex", MAX ( ytddata[index] ) )

Adding a new index:

Index =
CALCULATE (
    COUNTROWS ( 'Daily report table' ),
    FILTER (
        'Daily report table',
        'Daily report table'[OldIndex] <= EARLIER ( 'Daily report table'[OldIndex] )
    )
)

The output in my test:

 

Add an index column to a summarised table.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @wxqqaz,

 

Could you please tell me if your problem was resolved? Please mark the proper answer if it worked. That will be a help to the others.

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

Hi @wxqqaz,

 

If the "Date format" in the table is unique, you could try this formula. It works in my scenario.

 

index =
CALCULATE (
    COUNTROWS ( 'Daily report table' ),
    FILTER (
        'Daily report table',
        'Daily report table'[Date fromat]
            <= EARLIER ( 'Daily report table'[Date format] )
    )
)

Add an index column to a summarised table.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your speedy reply, unfortunately the date column in my case is not of unique values and therefore cannot be used as the criteria.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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