Solved: Accounts with both a failed payment AND a succeede... - Page 2

DAXisMyNemesis · ‎07-29-2019

Hi all

I don't really know how to go about searching for the solution for this as I'm not quite sure what the keywords should be...

I'm designing a model based on customer payments and am looking to produce figures for customers who have had a payment fall through ('payment reversed'), but have also made a payment successfully in the same month ('payment not reversed'). Not bothered about the order of events at this stage. I have measures to calculate counts for each separately, but I'm not sure how to go about isolating the inner portion of the Venn diagram!

I have a solution which filters based on measure outputs but the performance is horrible (as I knew it would be). I would have thought I should be

1. pulling a list of account IDs that have a reversal in the month
2. storing that in a VAR
3. applying that list of account IDs as a filter in a CALCULATE(count of successful payments)

Unfortunately my career in DAX is pretty young so I'm getting coder's block when I try to take that many steps in one fell swoop! I'm getting a mental block particularly around populating a VAR with a column of reversed Account IDs rather than pulling a full table, and it seems like a PITA to try and take a particular column out of a table VAR.

Help!

Anonymous · ‎08-06-2019

[Accounts with both Successful and Failed Direct Debits in Month] := 
CALCULATE(
	var __accountsWithSuccessfulPayment =
		calculatetable (
			summarize (
				TRANSACTIONS,
				ACCOUNTS[AccountID],
				DIMDATES[MonthEndDate]
			),
			TRANSACTIONS[HasBeenReversed_Int] = 0
		)
	var __accountsWithFailedPayment =
		calculatetable (
			summarize (
				TRANSACTIONS,
				ACCOUNTS[AccountID],
				DIMDATES[MonthEndDate]
			),
			TRANSACTIONS[HasBeenReversed_Int] = 1
		)
	var __accountsWithBoth =
		intersect (
			__accountsWithSuccessfulPayment,
			__accountsWithFailedPayment
		)
	return
		countrows ( __accountsWithBoth ),
	
	KEEPFILTERS(DIMTRANSACTIONTYPES[PaymentEntryTypeName] = "Payment Received"),
	KEEPFILTERS(DIMTRANSACTIONTYPES[PaymentMethodGroup]= "Direct Debit"),
	KEEPFILTERS(DIMTRANSACTIONCodes[GlobalAccountType] = "Mortgage"),
	
	USERELATIONSHIP(DIMDATES[DateKey], TRANSACTIONS[SKeyDatePeriodEnd])
)

Here's you measure withouth GROUPBY.

Best

Darek

View solution in original post

Accounts with both a failed payment AND a succeeded payment in a month?

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024