Forum Discussion

jmg80525's avatar
jmg80525
Helper II
1 year ago

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 ...
"DAX " "DAX help" 'Need help' 'DAX'
jmg80525's avatar
jmg80525
Helper II
1 year ago

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's avatar
    Deku
    Super User
    1 year ago
    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