If column has specific charge mark record

[kattlees]

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.

[erik_tarnvik]

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.

[kattlees]

Erik, 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
)

[kattlees]

Ok - this is what I did and it put a 0 in all the fields.

Off Formulary = IF(COUNTROWS(FILTER(V_ACCT_CHARGES,V_ACCT_CHARGES[AcctNum]=EARLIER(V_ACCT_CHARGES[AcctNum]) && V_ACCT_CHARGES[ChargeDescription]="KNEE MED I"))>0,1,0)

Where did I mess up?

[erik_tarnvik]

Are you searching for records where V_ACCT_CHARGES[ChargeDescription] is exactly equal to "KNEE MED I" or just records where V_ACCT_CHARGES[ChargeDescription] contains "KNEE MED I" but may also include other text? If the latter, replace 

V_ACCT_CHARGES[ChargeDescription]="KNEE MED I"

with 

FIND("KNEE MED I", V_ACCT_CHARGES[ChargeDescription],1,0) > 0

in your formula.

[kattlees]

I get this error

A single value for column 'AcctNum' in table 'V_ACCT_CHARGES' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

My formula is this:

OffForm = IF(COUNTROWS(FILTER(V_ACCT_CHARGES,V_ACCT_CHARGES[AcctNum]=EARLIER(V_ACCT_CHARGES[AcctNum]) && V_ACCT_CHARGES[ChargeDescription]="KNEE MED I"))>0,1,0)

Do I need to use the V_ACCT table where the acct number is only listed once?

[erik_tarnvik · Accepted solution]

You need to define OffForm as a calculated column in V_ACCT_CHARGES rather than as a measure.

[kattlees]

Sorry if this is a dumb question, but how do I do a calculated column? I right clicked on the table name and chose create new column and put the formula in.  I didn't choose measure.

[kattlees]

I was in the wrong table. Sorry.  It worked perfectly

[kattlees]

Where it is exact. Let me try your formula

[kattlees]

Do I put this in the table with the charges or with the single account number?

Table V_Acct has field ACCTNUM with unique account numbers for everyone

Table V_AcctCharges has the fields with charges. 

[erik_tarnvik]

Probably in the table with the charges. I assume that table also has a column for ACCTNUM that connects the two tables?

[kattlees]

Yes it does

[erik_tarnvik]

Just to be clear, you could put the new column in either table. If you put it in the main account table you would have to modify the FILTER formula but it would still work and actually be somewhat more efficient. But it depends on how you are going to use it which I can't really tell.

[kattlees]

How I want to use it is to who a line and cluster column chart that shows the total charges of a case that is considered "special" with the number of cases done

Then another one that shows the same thing with cases that are not considered "Special"