DAX statement that groups and creates index dynamically

[jmg80525]

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.

[jmg80525]

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]

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

[v-xinc-msft]

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]

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]

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

[Deku]

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

[jmg80525]

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]

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