Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smpa01
Super User
Super User

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)

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

 

 

smpa01_0-1674768343684.png

 

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 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 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.

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
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.

 

@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.

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
jeffrey_wang
Power BI Team
Power 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.

@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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.