indentify first instance based on multi criteria power bi

[Chandrashekar]

Hello,

I have requested for column formula in below post but unfortunately it is not working if I use Index in other table.

Here am trying to get first instance based on multi criteria. 

Sample Excel data with Formula: Sample Excel Data 

Sample PBIX file: PBIX 

Previous Post:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/indentify-first-instance-based-on-multi-crite...

[Anonymous]

Hi @Chandrashekar ，

Try this dax sytnax:

Result = 
VAR _Rank=RANKX('Table',IF(MOD([Index],2)=0,[Index]-1,[Index]),,ASC,Dense)
RETURN
IF(MOD(_Rank,2)=0,0,1)

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Chandrashekar]

Hello,

Thanks for the formula but it not matching my requirement when I use in large data.

How to use dax containing all 3 conditions as mentioned in image. I see dax shared from you is on Index column only.  

In excel am using =IF(COUNTIFS(B$2:B2,B2,D$2:D2,D2,C$2:C2,C2)=1,1,0).

[Chandrashekar]

Hello,

Can I get help on this?

Regards,

Chandrashekar B

[Anonymous · Accepted solution]

Hi @Chandrashekar ,

What is the specific conditional filtering that you mentioned that requires the use of three conditions?
Be aware that the formulas used in excel and power bi desktop are different.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Chandrashekar]

Hello,

Thanks for your reply. At last I got the solution to it. I concat 3 columns and took unique from it and now it is working fine.

Regards,

Chandrashekar B

[Chandrashekar]

Hello,

I am aware that there is relationship between tables. Is there any way I can achieve this as when I used in large data formula is not working.

Regards,

Chandrashekar B