cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
4DZiltoid
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:

CodeMonthProv.AmountFact.
474588221A10000
474588221A05
474522661B50000
1234562C2000
1234562C01


We add the new column and get:

CodeMonthProv.AmountFact.NewColumn
474588221A100005
474588221A051000
474522661B500000
1234562C20001
1234562C01200


Thanks!!

 

1 ACCEPTED SOLUTION
smpa01
Memorable Member
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]))

 

Capture.PNG

 


New Animated Dashboard: Sales Calendar


View solution in original post

2 REPLIES 2
smpa01
Memorable Member
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]))

 

Capture.PNG

 


New Animated Dashboard: Sales Calendar


View solution in original post

Amazing answer!!

Thanks a lot!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.