topic Re: DAX statement that groups and creates index dynamically in DAX Commands and Tips

DAX statement that groups and creates index dynamically

jmg80525 — Tue, 04 Mar 2025 18:06:34 GMT

Greetings:

I have a report where I utilized the Microsoft Gannt visual. In typical Gannt fashion it will put the Gannt element with the oldest date on top and then sort accordingly on date; top to bottom, left to right with the calendar hash on top.

It also has a feature where you can assign a "Parent" category to the individual Gantt items. When applying the Parent category the visual will find the Gantt element ("Child") with the oldest date and then group all of the other elements that correspond to the Parent and sort them. Effectively, it groups on the parent, find the parent with the oldest date, then sorts the elements within. Rinse and repeat.

I combined the Gannt visual on top with a table visual on the bottom of the page; that table visual providing a more structured grid of details. I created an index in Power Query that replicates the logic and apply the index to Sort By Column in the model. At initial load all is good. The sorting of the table matches the sorting of the Gantt. What I didn't consider is that when one changes the domain of start dates (i.e. show me all the elements whose start date is in month X) that Gannt visual does it group and sort again just fine, the table is still utilizing the unsliced, undynamic index built in Power Query. They are out of sorts.

What I'm trying to create and failing is a DAX statement where it would dynamically generate a index that matches the Gannt sort logic. It's a bit of hack, but the thinking is that when a user sliced the report and changed the domain of start dates, they could use the measurement persisted as a column on the table to make the table sort match the Gantt.

I've tried employing Greg_Deckler suggestion found here without sucess. Below are some redacted screen shots to provide some visual clues.

Re: DAX statement that groups and creates index dynamically

Deku — Tue, 04 Mar 2025 18:15:23 GMT

You can use SUBSTITUTEWITHINDEX to perform a multi column ranking. Add the measure to the table and sort the table by it. Then reduce the columns width until it is not visible

See this video by SQLBI

Re: DAX statement that groups and creates index dynamically

Greg_Deckler — Wed, 05 Mar 2025 21:01:53 GMT

jmg80525 Any chance you can provide a sample PBIX file or sample data? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: DAX statement that groups and creates index dynamically

jmg80525 — Fri, 07 Mar 2025 20:34:21 GMT

Here's a link to a sample PBIX. A precursory glance at SubstitueWithIndex documentation suggests that

This function does not guarantee any result sort order.

. To replicate the issue choose June on the calendar slicer. Note the reordering of the regions in the Gannt chart. You will see that I have ordered sorting on the RegionSort column and ProjectName uses StartDate

Re: DAX statement that groups and creates index dynamically

Deku — Fri, 07 Mar 2025 20:44:17 GMT

you provide a <orderBy_expression> so only can't guarantee sort order if there are ties

Re: DAX statement that groups and creates index dynamically

MarkLaf — Sun, 09 Mar 2025 05:36:51 GMT

As already mentioned, we would need a more specific sample to work with to troubleshoot the issue.

Speaking at a very high level, though, if you have a date slicer that filters the Gantt data, and you want it to similarly filter the table data, then you need to set up a relationship between Date -1--M-> Table the same as you should have a relationship between Date -1--M-> Gantt.

Re: DAX statement that groups and creates index dynamically

Anonymous — Mon, 10 Mar 2025 06:06:26 GMT

Hi jmg80525 ,

Did the replies offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regards,

Lucy Chen

Re: DAX statement that groups and creates index dynamically

jmg80525 — Wed, 12 Mar 2025 20:36:57 GMT

K. I tried running w/SubstituteWithIndex. I'm a bit over my DAX paygrade. I could get the code below to return a table in the DAX editor. But I can't figure out how to implement that as a measure that I drop into a visual. I reworked and simplified the PBIX. Here's a link.

DEFINE VAR A = SUMMARIZECOLUMNS( Sample[Region], -- Group by the Region column Sample[StartDate], -- Group by the StartDate column Sample[UniqueID] -- Include UniqueID to ensure uniqueness ) VAR B = SUMMARIZECOLUMNS( Sample[StartDate], -- Group by the StartDate column Sample[Region], -- Group by the Region column Sample[UniqueID] -- Include UniqueID to ensure uniqueness ) VAR Result = SUBSTITUTEWITHINDEX(A, "Magic", B, 'Sample'[UniqueID], ASC) EVALUATE A EVALUATE B EVALUATE Result

Could use a little additional help to get my over the hump.

Re: DAX statement that groups and creates index dynamically

Deku — Wed, 12 Mar 2025 22:02:33 GMT

measure = rnk = VAR A = ADDCOLUMNS( ALLSELECTED( Sample[Region], -- Group by the Region column Sample[StartDate], -- Group by the StartDate column Sample[UniqueID] -- Include UniqueID to ensure uniqueness ), "@start", CALCULATE(MAX(Sample[StartDate])), "@region", CALCULATE(MAX(Sample[Region])), "@id", CALCULATE(MAX(Sample[UniqueID])) ) VAR B = ALLSELECTED( Sample[Region], -- Group by the Region column Sample[StartDate], -- Group by the StartDate column Sample[UniqueID] -- Include UniqueID to ensure uniqueness ) VAR rnk = SUBSTITUTEWITHINDEX(A, "@rank", B, 'Sample'[UniqueID], ASC) RETURN MAXX( FILTER( rnk, SELECTEDVALUE( 'Sample'[Region] ) = [@region] && SELECTEDVALUE( 'Sample'[StartDate] ) = [@start] && SELECTEDVALUE('Sample'[UniqueID]) = [@id] ), [@rank] )

you still need to play with the sorting expression

Re: DAX statement that groups and creates index dynamically

Anonymous — Mon, 02 Jun 2025 19:43:13 GMT

Hi jmg80525

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: DAX statement that groups and creates index dynamically

Anonymous — Sat, 07 Jun 2025 17:07:10 GMT

Hi jmg80525
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Re: DAX statement that groups and creates index dynamically

Anonymous — Tue, 10 Jun 2025 10:08:13 GMT

Hi jmg80525
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.