Identify Split Transactions

[Anonymous]

Hello:

I need help with identifying split transactions in Power BI. In Tableau, I was able to develop a worksheet that could identify all potential split transactions by using the following formula: {fixed [Employee],[Vendor],DATETRUNC('day', [Transaction Date]):COUNTD([Payment Type])}. Essentially what the formula says is that is the employee, vendor, and the day is the same then count the distinct count the payment type used.  From there I was able to filter in Tableau all transactions that had multiple (more than 2) payment types. 

In Power Bi, I have tried putting in the following formula but it does not seem like it is working: 

Split Transactions = CALCULATE ( DISTINCTCOUNT(Page1_1[Payment Type]), ALL(Page1_1[Transaction Date].[Date]),SUMMARIZE(Page1_1,Page1_1[Employee],Page1_1[Vendor],Page1_1[Transaction Date].[Date]))

Can anyone help me to take what I have in Tableau and incorporate it into Power BI?

Thank you! 

Best regards,

M

[Fowmy]

Hi,

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Hi!

 Unfortunately, I am unable to share the data as it is company sensitive - please refer to the example below. What I am trying to do is identify any potential split transactions. A split transaction is when an employee is submitting expenses through different payment methods to avoid providing a receipt and stay under the policy limit. Please note that in the example below the employee name, vendor, and transaction date are the same. This result would allow us to identify which transactions we need to investigate further. 

Thanks,

M

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ，

Create a measure as below:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Payment Type]),ALLEXCEPT('Table','Table'[Employee name],'Table'[vendor],'Table'[Transaction date]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you! It seems like I can make it work with this formula 🙂