Classifying transaction based on date slicer

[czaldumbide]

Hi everyone, 

I need your help fguring out how to classify a record based on a date slicer. 

I'm attaching a PBIX file. Sample PBIX 

I have a transactions table where I have the columns of Date, ID, Transaction Code, Transaction Amount, and Remaining Balance. The code 'DD" refers to a decrease in an account. If the Remaining Balance is >0 , I classify it as a Decrease, but if the reamining Balance = 0 then I classify it as a Cancellation. This works perfectly when describing transactions day to day, but when I'm looking at a larger time period it gets a bit complicated as my teammates want to see it differently. Let me give you an example:

EX: Lets say one of my coworkers selects the dates between June 1st and 25th in the date slicer. He will then see the following tables.

Cancellations

ID	Transaction Amount
A	$100
B	$200
C	$100

Decrease

ID	Transaction Amount
C	$400
C	$50
D	$30

As you can see, first there were 2 payments from ID = C, and since it still had a remaining balance it was classified under Decrease, and then another transaction was made and the reamining balance was now 0 so it was classified as a cancellation. This is technically correct, but my coworkers would like to see this ID only under cancellations when this happens to avoid any confusion. So for this case the expected result would be:

Cancellations

ID	Transaction Amount
A	$100
B	$200
C	$550

Decrease

ID	Transaction Amount
D	$30

This is what I would like tab 1 of the sample PBIX file to look like. But for tab 2, my results are correct since my date slicer doesn't include the transaction from ID C that resulted in a cancellation. 

Let me know if you understood me correctly. Any help in this would be appreciated!

Thanks!

[v-alq-msft · Accepted solution]

Hi, @czaldumbide 

Based on the descirption, I assume that you want to display the record in ‘Decrease’ where 'ID' is not included in 'Cancellations'.  I created data to reproduce your scenario. The pbix file is attached in the end.

Cancellations:

Decrease:

You may create a measure as below.

Visual Control = 
IF(
    SELECTEDVALUE(Decrease[ID]) in DISTINCT(Cancellations[ID]),
    0,1
)

Finally you may put the measure in the visual level filter to get the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[czaldumbide]

Hi Allan,

Thank you for the sample pbix file.

I should have been a bit clearer on my post. The tables I displayed as Cancellations and Decrease are my report visualizations. The actual data comes from a single table called 'Transactions' were I inlcuded a calculated column to identify each transaction as decrease or cancellation. 

Now, in regard to your solution I saw that you were able to remove transactions belonging to ID=C from the Decrease table, but you're still not adding those amounts to the cancellations table. The amount for ID=C in the cancellation table should be $550, not $100. 

I am attaching a sample PBIX. Ideally, on my first tab I should be removing ID C from decrease and adding its amount to Cancellations, but tab 2 displays my data correctly since the slicer is set until 06/18 so the transaction from ID C where remaining balance =0 hasn't happened yet. 

Let me know if it's a bit more clear what I'm looking for now.  Let me know if you have any further suggestions.

Sample PBIX - Transactions classifications 

Thanks!