DAX equivalent to INDEX + EQUIV

[chanal]

Hi,

 I would like to know please the DAX equivalent to Excel function INDEX+EQUIV

 In my example, I have an interval of accounts, and if I have one account,  I want to search the code for it:

 the formula is : =INDEX($D$3:$D$10;EQUIV(1;($A$3:$A$5=MAX(($A$3:$A$5<=F3)*$A$3:$A$5))*($B$3:$B$5=MIN(SI($B$3:$B$5>=F3;$B$3:$B$5)));0))

Condition

Accounts: lower limit   Accounts: upper limit    Code_Balance sheet         Exemple: Account Number         Result with Index+equiv

28000000                       29000000                      Balance_Sheet                         28500000                              Balance_Sheet

 If it is possible, can we add the function IF, such as: 

=SI(I3-J3>0;INDEX($F$3:$F$9;EQUIV(1;($B$3:$B$9=MAX(($B$3:$B$9<=H3)*$B$3:$B$9))*($C$3:$C$9=MIN(SI($C$3:$C$9>=H3;$C$3:$C$9)));0)))

Moreover, if I import my correspondence table in Power BI, what would be the primary key? The accounts number?

 Thank you so much if you would reply to my query,

xx

[v-ljerr-msft]

Hi @chanal,

Could you post your table structures with some sample/mock data and your expected result, so that we can better assist on this issue? 

Regards

[chanal · Accepted solution]

Hello,

sorry for the delay of my answer. with holidays, difficult to have wifi connection...

Here is the sample I wanted to put in DAX:

http://www.coachandschool.com/wp-content/uploads/2017/08/Table-de-correspondance-avec-SIINDEXEQUIV.x...

Me and my friend, we have found the solution for finding the CR:

Code_CR_N5 = CALCULATE(VALUES(CR[Code_CR_N5]);FILTER(CR;Champs[CompteNum]>=CR[From] && Champs[CompteNum]<=CR[To]))

this is a banding formula. We found the solution in this website:

http://exceleratorbi.com.au/banding-in-dax/

Thank you.