Forum Discussion

Coryanthony's avatar
Coryanthony
Helper III
3 years ago

Filter measure

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.

 

 

Need Help

7 Replies

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    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's avatar
      Coryanthony
      Helper III
      3 years ago

      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_'s avatar
        vicky_
        Super User
        3 years ago

        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.