Forum Discussion

yaowen's avatar
yaowen
Frequent Visitor
4 years ago
Solved

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

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 

SubstituteWithIndex
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

5 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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's avatar
      yaowen
      Frequent Visitor
      4 years ago

      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's avatar
        daXtreme
        Solution Sage
        4 years ago

        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.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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.