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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ak77
Post Patron
Post Patron

Rank for Summarize Table

Hi All, 

Need a help i need to include a column in my summarize table which basically gives Rank/Index/Serial No. (1,2,3....)  when displayed. Below is the summary Table. Can anyone please help

 

SUMMARIZE(
filter(ODATA_LINKS, ODATA_LINKS[SourceType] <> BLANK() ),
ODATA_LINKS[SourceWorkItemId],ODATA_LINKS[Complete_ChildCount],ODATA_LINKS[InterfaceNumber],ODATA_LINKS[team],
ODATA_LINKS[Parent],
ODATA_LINKS[SourceTitle],ODATA_LINKS[ExtractedTitle],ODATA_LINKS[CalsourceEndDate],ODATA_LINKS[CalsourceLikely],ODATA_LINKS[CalsourceProb],ODATA_LINKS[Total_Count],
ODATA_LINKS[sourceEndDate],
ODATA_LINKS[sourceLikley],
ODATA_LINKS[sourceProb],
ODATA_LINKS[SourceOutcome],ODATA_LINKS[Milestone_Count],ODATA_LINKS[NotStarted_ChildCount]

)

2 ACCEPTED SOLUTIONS
MNedix
Super User
Super User

Heya,

You could simply add a calculated column in your virtual table, as below:

 

....
ODATA_LINKS[SourceOutcome],ODATA_LINKS[Milestone_Count],ODATA_LINKS[NotStarted_ChildCount]
"Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
)

 

This will create an index column based on the ODATA_LINKS[Parent] column. You can change this based on needs.

 

PS: I would start the formula with FILTER first - like this:

Virtual table = 
FILTER(
SUMMARIZE(
ODATA_LINKS, ODATA_LINKS[SourceType], ODATA_LINKS[SourceWorkItemId], ODATA_LINKS[Complete_ChildCount], ODATA_LINKS[InterfaceNumber], ODATA_LINKS[team],
ODATA_LINKS[Parent], ODATA_LINKS[SourceTitle], ODATA_LINKS[ExtractedTitle], ODATA_LINKS[CalsourceEndDate], ODATA_LINKS[CalsourceLikely], ODATA_LINKS[CalsourceProb], ODATA_LINKS[Total_Count], ODATA_LINKS[sourceEndDate], ODATA_LINKS[sourceLikley],
ODATA_LINKS[sourceProb], ODATA_LINKS[SourceOutcome], ODATA_LINKS[Milestone_Count], ODATA_LINKS[NotStarted_ChildCount], "Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
ODATA_LINKS[SourceType] <> BLANK()
)

 

Hope it helps.

 



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

View solution in original post

Jihwan_Kim
Super User
Super User

Hi, 

I am not sure how your expected outcome looks like, but I tried to craete a sample pbix file like below.

It is for creating a new table with adding one more column that shows Row Number.

 

Jihwan_Kim_0-1729220303462.png

 

The row number is based on more than one column.

 

ROWNUMBER function (DAX) - DAX | Microsoft Learn

 

New table with additional column = 
VAR _t =
    SUMMARIZE (
        ODATA_LINKS,
        ODATA_LINKS[SourceType],
        ODATA_LINKS[InterfaceNumber],
        ODATA_LINKS[Parent],
        ODATA_LINKS[ExtractedTitle],
        ODATA_LINKS[NotStarted_ChildCount],
        ODATA_LINKS[Milestone_Count],
        ODATA_LINKS[Complete_ChildCount],
        ODATA_LINKS[Total_Count]
    )
VAR _addrownumber =
    ADDCOLUMNS (
        _t,
        "RowNumber",
            ROWNUMBER (
                _t,
                ORDERBY (
                    ODATA_LINKS[NotStarted_ChildCount], DESC,
                    ODATA_LINKS[Milestone_Count], DESC,
                    ODATA_LINKS[Complete_ChildCount], DESC,
                    ODATA_LINKS[Total_Count], DESC
                )
            )
    )
RETURN
    _addrownumber

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

3 REPLIES 3
ak77
Post Patron
Post Patron

Thanks @Jihwan_Kim and @MNedix  for ur efforts.

 

Both solution worked. ! Closing this issue

Jihwan_Kim
Super User
Super User

Hi, 

I am not sure how your expected outcome looks like, but I tried to craete a sample pbix file like below.

It is for creating a new table with adding one more column that shows Row Number.

 

Jihwan_Kim_0-1729220303462.png

 

The row number is based on more than one column.

 

ROWNUMBER function (DAX) - DAX | Microsoft Learn

 

New table with additional column = 
VAR _t =
    SUMMARIZE (
        ODATA_LINKS,
        ODATA_LINKS[SourceType],
        ODATA_LINKS[InterfaceNumber],
        ODATA_LINKS[Parent],
        ODATA_LINKS[ExtractedTitle],
        ODATA_LINKS[NotStarted_ChildCount],
        ODATA_LINKS[Milestone_Count],
        ODATA_LINKS[Complete_ChildCount],
        ODATA_LINKS[Total_Count]
    )
VAR _addrownumber =
    ADDCOLUMNS (
        _t,
        "RowNumber",
            ROWNUMBER (
                _t,
                ORDERBY (
                    ODATA_LINKS[NotStarted_ChildCount], DESC,
                    ODATA_LINKS[Milestone_Count], DESC,
                    ODATA_LINKS[Complete_ChildCount], DESC,
                    ODATA_LINKS[Total_Count], DESC
                )
            )
    )
RETURN
    _addrownumber

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

MNedix
Super User
Super User

Heya,

You could simply add a calculated column in your virtual table, as below:

 

....
ODATA_LINKS[SourceOutcome],ODATA_LINKS[Milestone_Count],ODATA_LINKS[NotStarted_ChildCount]
"Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
)

 

This will create an index column based on the ODATA_LINKS[Parent] column. You can change this based on needs.

 

PS: I would start the formula with FILTER first - like this:

Virtual table = 
FILTER(
SUMMARIZE(
ODATA_LINKS, ODATA_LINKS[SourceType], ODATA_LINKS[SourceWorkItemId], ODATA_LINKS[Complete_ChildCount], ODATA_LINKS[InterfaceNumber], ODATA_LINKS[team],
ODATA_LINKS[Parent], ODATA_LINKS[SourceTitle], ODATA_LINKS[ExtractedTitle], ODATA_LINKS[CalsourceEndDate], ODATA_LINKS[CalsourceLikely], ODATA_LINKS[CalsourceProb], ODATA_LINKS[Total_Count], ODATA_LINKS[sourceEndDate], ODATA_LINKS[sourceLikley],
ODATA_LINKS[sourceProb], ODATA_LINKS[SourceOutcome], ODATA_LINKS[Milestone_Count], ODATA_LINKS[NotStarted_ChildCount], "Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
ODATA_LINKS[SourceType] <> BLANK()
)

 

Hope it helps.

 



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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