Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Why is not Function SubstituteWithIndex() able to detect common columns between its operands that could be used for join?Please guide and prompt the reasons for such errors. Thank you
I follow the method of adding index columns, but I get an error:
SubstituteWithIndex() is not able to detect common columns between its operands that could be used for join.
However, if you add one more line of code, it will become a virtual table, and then add an index column to calculate the table,the result is correct result:
Please give me directions when it's convenient. Thank you
Solved! Go to Solution.
Hi @yaowen
Thanks for reaching out to us.
>> Why is not Function SubstituteWithIndex() able to detect common columns between its operands that could be used for join?Please guide and prompt the reasons for such errors.
-
In my opinion, according to the definition of SubstituteWithIndex(), "it returns ...of the two tables supplied as arguments", so we need to pass 2 tables as arguments to it. In INDEX2, you defined a virtual table _Product, however, in INDEX1, there is only one table. So I guess that's why it's giving an error.
FYI: SUBSTITUTEWITHINDEX function (DAX) - DAX | Microsoft Docs
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @yaowen
Thanks for reaching out to us.
>> Why is not Function SubstituteWithIndex() able to detect common columns between its operands that could be used for join?Please guide and prompt the reasons for such errors.
-
In my opinion, according to the definition of SubstituteWithIndex(), "it returns ...of the two tables supplied as arguments", so we need to pass 2 tables as arguments to it. In INDEX2, you defined a virtual table _Product, however, in INDEX1, there is only one table. So I guess that's why it's giving an error.
FYI: SUBSTITUTEWITHINDEX function (DAX) - DAX | Microsoft Docs
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @yaowen
Just a question: Why do you have to use this function? Truth be told, for all the time that I've been programming in DAX (and that's about 6-7 years now), I've never had a need to use this function. Not a single time.
Such as: Performance optimization of powerbi maximum continuous element number algorithm.
The idea of the staggered element method is to stagger the original elements, and then calculate the start and end marks. Here, changing the vertical comparison to the horizontal comparison, which can greatly reduce the number of iterations. Therefore, the algorithm achieves significant performance improvement
VAR MyData = SELECTCOLUMNS( '10W' , "OrginIndex" , [Item] , "Value" , [Flag] )
VAR Add1 = ADDCOLUMNS( MyData , "Value2" , SELECTCOLUMNS( FILTER( MyData , [OrginIndex] = EARLIER( [OrginIndex] ) - 1 ) , "Value" , [Value] ) )
VAR Add1Filterd = FILTER( Add1 , [Value] + [Value2] = 1 )
VAR CreateBegin = ADDCOLUMNS( Add1Filterd , "BeginIndex" , IF( [Value] = 1 , [OrginIndex] ) )
VAR CreateEnd = ADDCOLUMNS( CreateBegin , "EndIndex" , IF( [Value2] = 1 , [OrginIndex] ) )
VAR CreateIndex = SUBSTITUTEWITHINDEX( CreateEnd , "Index" , SELECTCOLUMNS( CreateEnd , "OrginIndex" , [OrginIndex] ) , [OrginIndex] , ASC )
VAR AddAdd = ADDCOLUMNS( CreateIndex , "NewEndIndex" , SELECTCOLUMNS( FILTER( CreateIndex , [Index] = EARLIER( [Index] ) + 1 ) , "NewEndIndex" , [EndIndex] ) )
VAR AddAddFilterd = FILTER( AddAdd , ISBLANK( [EndIndex] ) )
RETURN MAXX( AddAddFilterd , [NewEndIndex] - [BeginIndex] )
Among all kinds of optimization, adding index is a very common and immediate optimization method. DAX provides this function to select the future table for the user to add indexes, so I want to understand SUBSTITUTEWITHINDEX. Thank you!
Wait... If you don't create a measure and trying to do it in a table, then why don't you do this easily in Power Query? In Power Query it'll not only be fast but easy as well.
Dynamically add index sometimes
Instead of adding an index in Power Query in advance, the index sometimes should be added in DAX. This is because it can sometimes help this kind of dynamic increase index calculation: dynamic calculation that always occurs after the user selects.
That is to say, if the dynamic calculation involves adding an index after the user selects it, and it is impossible to add an index in Power Query in advance, then the calculation after the user selects it depends on the user's selection and cannot be predicted in advance, so it is called dynamic.
Therefore, it is very valuable to understand that SUBSTITUTEWITHINDEX and DAX provide this function to select the future tables for users to add indexes. Thank you .
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |