Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to Solution.
@Anonymous 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]))
@Anonymous 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]))
Amazing answer!!
Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |