Index column

[smpa01]

Is it possible to create an index column in DAX out of thin air, like it is doable in SQL

drop table if exists #test

select * into #test
from
(values('a'),('1'),(null),('xxx'),('-100')) t (a)

select *, row_number() over (order by (select null)) from #test

I am using DAX for data transformation from a SSAS db, fact table contains 12M+ rows, PQ is not an option due to performance, datamart is turned off.

I looked into this , not perfoming well with large table.

Can the new WINDOW function do the tricks?

@Greg_Deckler @jeffrey_wang @CNENFRNL @AlexisOlson 

[AlexisOlson]

What problem do you need an index column to solve? It doesn't seem like an index for a table this size is feasible but maybe there's another way to solve whatever issue you want an index for.

@Greg_Deckler's approach is a neat use of CONCATENATEX and PATHITEM but won't scale. In Chris Webb's article, What Is The Maximum Length Of A Text Value In Power BI? , he mentions that

DAX functions such as ConcatenateX() put a limit on the length of the text values that they can work with at around 2.1 million characters

citing @jeffrey_wang.

[smpa01 · Accepted solution]

@AlexisOlson  I need a row identifier cause I am spliting a factTable[Field] to a new table, with schema VARCHAR (e.g. a|b|c / e|f|g|h) using PATHITEM, which is why I need a row identifier between factTbl and derivedFactTbl to create relationship (1:M).

@Greg_Deckler  has a great solution which works very well on a narrow table (n X 1) but not on a wide table (n X m). Nevertheless, thank you.

I managed to create a surrogateID by concatenating all the columns particpating in SUMMARIZECOLUMNS and then I created derivedTable with that SID.

[Greg_Deckler]

@smpa01 Yes: The Mythical DAX Index - Microsoft Power BI Community

[smpa01]

@Greg_Deckler  thanks for this, will let you know how it goes for me

[jeffrey_wang]

We are working on such a function but it may not be exactly what you need. The function is just meant to provide a convenient way to identify rows by a single key column instead of composite key columns. If the table has duplicate rows, it won't dedupe them but will raise an error instead. In other words, the function will only assign a unique value if the original rows are already unique on their own. Moreover, window functions work in the formula engine, not in the Vertipaq Engine. If the underlying table contains a large number of rows, performance will suffer as data is read into the formula engine space before functions are calculated. DAX is not meant to be an ETL tool at all, but rather a calculation engine over filtered and aggregated data after the ETL has happened. Although users have used DAX to perform ETL functions, that's not the design and would only work as a convenience feature over small datasets. True ETL should happen before data is imported.

[smpa01]

@jeffrey_wang I truly second your opinion about DAX not being intended for ETL. In a perfect world, I would be taking the source data from some db and analyse with DAX.

Unfortunately,  I am working on a particular project with a company where we have tons of limitations (due to strict security issues each and every feature release goes through red tape before being available to the workspace) which is why a lot of features are turned off (e.g. Datamart could have easily bailed me out of this trouble I am having now). Hence , to get past that limitation and to yet achieve scalability, efficiency and ultimately deliver the project, I am glad DAX has that capability - both Transformation and Analysis, with blazing fast speed.

I was hoping that the newly introduced WINDOW/INDEX/OFFSET could somehow help me out in this situation.

Anyway, thanks for the response.