topic Sort Multiple Parent Rows with Children Rows in DAX Commands and Tips

Sort Multiple Parent Rows with Children Rows

YIAX — Sat, 28 Sep 2024 16:34:14 GMT

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

Re: Sort Multiple Parent Rows with Children Rows

lbendlin — Sat, 28 Sep 2024 11:52:04 GMT

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

Re: Sort Multiple Parent Rows with Children Rows

Anonymous — Fri, 11 Oct 2024 05:50:27 GMT

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.

Re: Sort Multiple Parent Rows with Children Rows

YIAX — Mon, 30 Sep 2024 13:39:15 GMT

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.