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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yaowen
Frequent Visitor

Why is not Function SubstituteWithIndex() able to detect common columns?

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

1.png

Spoiler
  

3.png

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:

2.png

Please give me directions when it's convenient. Thank you 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1661242953935.png

 

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.

View solution in original post

5 REPLIES 5
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1661242953935.png

 

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.

daXtreme
Solution Sage
Solution Sage

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.

4.png

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

6.png

 

 

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!

@yaowen 

 

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 .

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.