Forum Discussion
Post PatronIf column has specific charge mark record
I have a column with account number
Another column has charges for that account number. There could be 100 charges per account number.
Charges have description, quantity and amount as well as other things.
I would like to search the charges for a specific charge description and if it is there, add a column to table with account number with a value of 1, if it is not there mark it as a value of 0.
What I'm trying to do is get a total cost vs charge for people with this specific charge and a total cost vs charge for people without this specific charge.
You need to define OffForm as a calculated column in V_ACCT_CHARGES rather than as a measure.
15 Replies
- erik_tarnvik
Solution Specialist
This is easily done in Power Query. Add a Conditional Column that outputs 1 for any item in another column that contains the text you are looking for, and 0 otherwise.
kattleesErik, I have tried the "if column = then 1 else 0 but it puts it in the table with the charges and then when I go to sum charges for those accounts, it only sums the column that has the 1.
Maybe I"m not following you.
Examples:
Account Desc Amount
123456 Charge 1 100
123456 Charge 2 125
123456 Charge 3 250
654321 Charge 1 100
654321 Charge 3 250
654321 Charge 4 300
I can get it to put a new column, lets say I call it "special" next to amount if desc = Charge 2
But when I go to get total charges for all with "special" as 1, it is only totalling the charges with a description of charge 2
I need any charge with that account number to be marked as 1 so my totals are correct.
I hope I am making sense.
- erik_tarnvik
Yes I think I understand now. Try defining Special as follows:
Special = IF ( COUNTROWS ( FILTER ( Data, Data[Account] = EARLIER ( Data[Account] ) && Data[Desc] = "Charge 2" ) ) > 0, 1, 0 )