Sort Multiple Parent Rows with Children Rows

[YIAX]

I am wanting to sort a table that contains multiple parent rows and children along with them using a dax measurement.

I am wanting to sort something like this in a table visual.

I am wanting to create the column SORT to actually sort my rows with parent and children. Parent Item would be 2.1 and its child would be 2.11 and so on. But not sure how to do that.

ITEM_ID	PARENT_ID	SORT	IsParent
AA		2.1	TRUE	PARENT
A0	AA	2.11	FALSE	CHILD
A1	AA	2.11	FALSE	CHILD
A2	AA	2.11	FALSE	CHILD
AB		2.2	TRUE	PARENT
A3	AB	2.21	FALSE	CHILD
AC		2.3	TRUE	PARENT
A4	AC	2.31	FALSE	CHILD

[v-yilong-msft]

Hi @YIAX ,

As Ibendlin said , depending on your example you can choose to use Matrix. in Matrix if you don't make a fine distinction between subcolumns it will make them aggregate.

So I create a table as you mentioned.

Then I create two calculated columns.

SortOrder =
VAR _ParentSort =
    IF (
        ISBLANK ( 'Table'[PARENT_ID] ),
        'Table'[SORT],
        LOOKUPVALUE ( 'Table'[SORT], 'Table'[ITEM_ID], 'Table'[PARENT_ID] )
    )
RETURN
    _ParentSort & "_" & 'Table'[SORT] & "_" & 'Table'[ITEM_ID]

Column = IF('Table'[IsParent]="TRUE",'Table'[ITEM_ID] ,'Table'[PARENT_ID])

Next we can put them into the Matrix visual.

Best Regards

Yilong Zhou

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

[YIAX]

In your solution, the sort column already exists. I am trying to create the sort column in this scenario. Then will display my data in a regular table visual not a matrix.

[lbendlin]

You can't sort AA, A1 and A2 all the same. At a minimum use 2.11, 2.11.1 and 2.11.2