Filter measure

[Coryanthony]

Hello,

I want to filter out debit and credits from table, either in power query or dax formula (both if all possible 🙂 . Any suggestions? If the transaction was refunded entirely (Yellow highlight), i want to filter it off the report. If it was a partial refund (green highlight, i want to see it. 

Please help.

[vicky_]

At the moment, neither Power Query nor Power BI can read highlighted cells. Unless there's some other logic that I'm missing, I think the easiest option is to just create a new column in the data source to record whether the transaction has been fully or partially refunded, and then use a slicer with that column's values.

[Coryanthony]

Hi @vicky_ 

Thank you for the response Vicky. I only highlighted the columns for easier display. 

If a create a new column with the fully, or partailly refund. Then I can filter out the fully refund transaction right?

Any suggestion you can provide for the new column logic? 

[vicky_]

Hey, 

Is the Description column unique to each purchase / refund? If so, you could do something in DAX like:

SWITCH(TRUE()
    CALCULATE(COUNT(description), ALLEXCEPT(Table, [Description]) > 1 &&
    CALCULATE(SUM([Transaction Amount]), ALLEXCEPT(Table, [Description]) > 0, "Partial Refund", 
    CALCULATE(COUNT(description), ALLEXCEPT(Table, [Description]) > 1 &&
    CALCULATE(SUM([Transaction Amount]), ALLEXCEPT(Table, [Description]) = 0, "Full Refund", 
    BLANK()
)

To explain the logic behind the code - the COUNT(description) looks if the same product has 2 entries (assuming that each description is unique, then if the person purchases another of the same product, then there would be some kind of increment counter in the description. This is a massive assumption though). And then summing the amount - if it's a partial refund, then the amount paid should be greater than the amount refunded, hence the >0 condition.

Hope that makes sense. 

[Coryanthony]

Any suggestion if not unique?

"uber" discription would be on every incurred transaction. Debit and Refunds. Typically, there would be more debited and refund. 

[Coryanthony]

@vicky_  
The Discription column is typically the same ("Uber" charge me $10, "Uber" refunded me -$10). Normally, Discription (Vendor) would be on both Debit and Refund transactions. 

OMG - thank you again Vicky. You're awesome. I think i can work with what you advised. 

[Coryanthony]

@vicky_ 

Any suggestion if not unique?

Discription (Vendor) would be every incurred transaction. Debit and Refunds. Typically, there would be more debited and refund. I would think the measure would not be accurate (inflating partial refund)

[vicky_]

Nope, unfortunately if there's no unique description then it would be very difficult to tell which transactions are related. Either get a column that's unique or think of other ways to link the transactions (e.g. combine date and description or something like that)