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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jmg80525
Helper II
Helper II

DAX statement that groups and creates index dynamically

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.

Second.pngThird.png

11 REPLIES 11
v-priyankata
Community Support
Community Support

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.

v-priyankata
Community Support
Community Support

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.

v-priyankata
Community Support
Community Support

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.

jmg80525
Helper II
Helper II

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. 

Deku
Community Champion
Community Champion

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

Deku_0-1741816946565.png

 


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

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

MarkLaf
Memorable Member
Memorable Member

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.

Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Deku
Community Champion
Community Champion

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

Deku
Community Champion
Community Champion

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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