cancel
Showing results for
Search instead for
Did you mean: New Member

## New column to get complementary row data if multiple criteria is met

Hi,

I want to create a new column within the same table based in 3 criterias&columns that must be met:
-Same Code
-Same Month
-Same Prov.
If they are equal, then the new column will contain the missing data between another 2 columns: Amount and Fact.

Example Table:

 Code Month Prov. Amount Fact. 47458822 1 A 1000 0 47458822 1 A 0 5 47452266 1 B 5000 0 123456 2 C 200 0 123456 2 C 0 1

We add the new column and get:

 Code Month Prov. Amount Fact. NewColumn 47458822 1 A 1000 0 5 47458822 1 A 0 5 1000 47452266 1 B 5000 0 0 123456 2 C 200 0 1 123456 2 C 0 1 200

Thanks!!

1 ACCEPTED SOLUTION  Memorable Member

@4DZiltoid  try this as a new column

``````X =
IF (
[Fact.] = 0
&& CALCULATE (
MAX ( tx[Fact.] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
) <> 0,
CALCULATE (
MAX ( tx[Fact.] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
),
IF (
[Amount] = 0
&& CALCULATE (
MAX ( tx[Fact.] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
) <> 0,
CALCULATE (
MAX ( tx[Amount] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
)
)
)``````

breakdown with three 3 calculated columns

``````_maxfact = CALCULATE(MAX(tx[Fact.]),ALLEXCEPT(tx,tx[Code],tx[Month],tx[Prov.]))

_maxamt = CALCULATE(MAX(tx[Amount]),ALLEXCEPT(tx,tx[Code],tx[Month],tx[Prov.]))

New Column = Column = IF([Fact.]=0&&[_maxfact]<>0,[_maxfact], IF([Amount]=0&&[_maxfact]<>0,[_maxamt]))
`````` New Animated Dashboard: Sales Calendar

2 REPLIES 2  Memorable Member

@4DZiltoid  try this as a new column

``````X =
IF (
[Fact.] = 0
&& CALCULATE (
MAX ( tx[Fact.] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
) <> 0,
CALCULATE (
MAX ( tx[Fact.] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
),
IF (
[Amount] = 0
&& CALCULATE (
MAX ( tx[Fact.] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
) <> 0,
CALCULATE (
MAX ( tx[Amount] ),
ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
)
)
)``````

breakdown with three 3 calculated columns

``````_maxfact = CALCULATE(MAX(tx[Fact.]),ALLEXCEPT(tx,tx[Code],tx[Month],tx[Prov.]))

_maxamt = CALCULATE(MAX(tx[Amount]),ALLEXCEPT(tx,tx[Code],tx[Month],tx[Prov.]))

New Column = Column = IF([Fact.]=0&&[_maxfact]<>0,[_maxfact], IF([Amount]=0&&[_maxfact]<>0,[_maxamt]))
`````` New Animated Dashboard: Sales Calendar New Member

Amazing answer!!

Thanks a lot!  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (3,899)