cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chandrashekar
Helper V
Helper V

indentify first instance based on multi criteria power bi

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

1 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

View solution in original post

6 REPLIES 6
v-mengzhu-msft
Community Support
Community Support

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)

 

vmengzhumsft_0-1664255243579.png

 

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

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_0-1664258923262.png

 

Hello,

 

Can I get help on this?

 

Regards,

Chandrashekar B

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

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
Helper V
Helper V

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.