Forum Discussion
Why is not Function SubstituteWithIndex() able to detect common columns?
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.
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 .