Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

Index 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)...
Need Help
  • smpa01's avatar
    smpa01
    3 years ago

    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.

     

jeffrey_wang's avatar
jeffrey_wang
Power BI Team
3 years ago

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's avatar
    smpa01
    Community Champion
    3 years ago

    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.