Forum

DAX formula in Powe...
 
Notifications
Clear all

DAX formula in PowerPivot: If rows regardless of row number have matching fields.

5 Posts
2 Users
0 Reactions
104 Views
(@countryfan_nt)
Posts: 18
Eminent Member
Topic starter
 

Hello friends, Hope all is well! please help me create a DAX formula.
As you can see in the below image, I need the formula to place an X mark if there are matching fields at the

last column on the rows that have the same:

A. Invoice Number.
B. Branch Name.
C. The amount matches but one in positive and the other is negative.

Thank you in advance for your time & efforts.

1669820957017.jpg

 
Posted : 02/12/2022 9:16 am
(@debaser)
Posts: 836
Member Moderator
 

If I understand correctly, you could use this:

 

=IF(COUNTROWS(FILTER(Table1,[Invoice]=EARLIER([Invoice])&&[Branch]=EARLIER([Branch])&&[Amount]=-EArlier([Amount])))>0,"X",blank())

 
Posted : 06/12/2022 5:56 am
(@countryfan_nt)
Posts: 18
Eminent Member
Topic starter
 

Dear Velouria, that was awesome, may I please ask for one more modification?

if amount results are ZERO no X. the formula should only work on netting out figures, e.g. 150 VS -150 etc

 

Thank you so much

 
Posted : 07/12/2022 5:44 am
(@debaser)
Posts: 836
Member Moderator
 

You should be able to just wrap another IF:

 

=IF([Amount]<>0,IF(COUNTROWS(FILTER(Table1,[Invoice]=EARLIER([Invoice])&&[Branch]=EARLIER([Branch])&&[Amount]=-EArlier([Amount])))>0,"X",blank()),BLANK())

 
Posted : 08/12/2022 4:50 am
(@countryfan_nt)
Posts: 18
Eminent Member
Topic starter
 

Truly a ROCKSTAR! Helped me learn something new

 
Posted : 08/12/2022 8:48 am
Share: