Forum Discussion
Community ChampionIndex column
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 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.
6 Replies
jeffrey_wangPower BI Team
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.
smpa01jeffrey_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.
- Greg_Deckler
- smpa01
Greg_Deckler thanks for this, will let you know how it goes for me
- AlexisOlson
Super User
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
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.
