The Mythical DAX Index 05-17-2020 12:37 PM - last edited 05-18-2020 06:26 AM

[Greg_Deckler]

It's weird where you find inspiration sometimes. I mean truly mind boggling. Recently, my current inspiration came from this seemingly innocuous thread. The author had implemented a rather nifty little DAX bubble sort that was obviously very tedious (because it's DAX trying to do a bubble sort) and was asking for help and advice on DAX table functions that could make the code cleaner and tidier. Turns out, the entire complex thing could be replaced with a single line of DAX code because CONCATENATEX includes an "Order by" feature that will sort your values for you. 

So, the answer was to just use CONCATENATEX, a function obviously purpose built for this exact scenario. But then I was reprimanded for using a purpose built function to accomplish the exact task for which it was designed. 😞  But, because my brain works weird, instead of letting this make me sad, I decided to use it as an opportunity to invent something that I TRULY should be reprimanded for! 🙂

And hence, the mythical DAX index. Let me just start by saying that you most certainly should NOT be using this and instead use Power Query to create an Index column. There is almost no use for this technique except that I CAN think of one, a scenario where you have a calculated table that you need an index on. Why would you have one of those? I don't know but it has come up before in these forums. Hmmm, and now I am thinking that this technique could have come in extremely handy for my TRIMMEAN function, I may have to revisit that one.

In any case, this approach is superior to any that involves RANKX because it deals with duplicates flawlessly. It actually combines the CONCATENATEX approach with the approaches I was using converting numbers and strings to tables while I was working on Excel to DAX Translation. So basically, when you feed CONCATENATEX a table of values and do not specify a sort order, CONCATENATEX simply processes them in row order within the table. So, when you combine this with GENERATESERIES and PATHITEM, you can actually create an index that mimics what you would get in Power Query, an index that starts at 0 or 1 for the first row of data and increments up until the last row of data. Nifty.

So @gtamir , sorry this took over a year to get you a simple DAX index per your request.

DAX Indexed Table = 
    VAR __SourceTable = 'Table'
    VAR __Count = COUNTROWS(__SourceTable)
    VAR __SortText = CONCATENATEX('Table',[Product],"|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,__Count,1),
            "Product",PATHITEM(__SortText,[Value],TEXT)
        )
RETURN
    __Table

NOTE: There are some interesting things that happen when you have duplicates. If you do not do any sorting, DAX groups duplicates together instead of keeping them in their places. So, definite variation from a Power Query index. But when you sort, you get true unique numbers for duplicates instead of how RANKX handles duplicates which, honestly, can be really infuriating sometimes...

eyJrIjoiN2ZkNTA0YjktMWQxZi00MjJlLTg4M2MtYjE5YmM2ZDdkM2I1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[JOSELUISMTZRMZ1]

Amazing! thank you very much it's what I'm looking for, As a meause I applied on mine but I'm getting the message

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

I tried with a column with no duplicated values but message persists

Any idea why?

[JOSELUISMTZRMZ1]

Oooops because of meausure expects one single value , lol. It has to be a table only, doesn't it?

[Greg_Deckler]

@JOSELUISMTZRMZ1 Correct, this is for creating a table. It's possible to create this as a table var in a measure and then theoretically if you have a unique identifier you could essentially look up the value of the index.

[gtamir]

@Greg_Deckler Well.... I am honored to have a special quick measure written for me. Thanks, I'll try it soon.