cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kattlees Member
Member

If 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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
erik_tarnvik Established Member
Established Member

Re: If column has specific charge mark record

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

15 REPLIES 15
erik_tarnvik Established Member
Established Member

Re: If column has specific charge mark record

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 Member
Member

Re: If column has specific charge mark record

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 Established Member
Established Member

Re: If column has specific charge mark record

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 Member
Member

Re: If column has specific charge mark record

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 Established Member
Established Member

Re: If column has specific charge mark record

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

kattlees Member
Member

Re: If column has specific charge mark record

Yes it does

erik_tarnvik Established Member
Established Member

Re: If column has specific charge mark record

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 Member
Member

Re: If column has specific charge mark record

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?

kattlees Member
Member

Re: If column has specific charge mark record

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"