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

[yaowen]

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

Spoiler

  

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 

[v-xiaotang · Accepted 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.

[daXtreme]

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.

[yaowen]

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!

[daXtreme]

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

[yaowen]

       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 .