Forum Discussion
zausten
3 years agoHelper I
Filtering dataset based on 2 two conditions
Hi there, The scenario I have is I have a list of transactions for multiple customers, date ranges . I require a filter on this dataset to meet two conditions. 1. The customer must have transacted ...
halfglassdarkly
3 years agoResponsive Resident
It's a bit hard to envision without knowing your table structure and how you're planning to use this, but (without having tested) you could probably use something along these lines:
Measure Name =
//Store customer ID inherited from current row/visual/slicer context
Var CID = max('table'[Customer ID])
//Store Month inherited from current row/visual/slicer context (assumes you have a column for month already)
Var M = max('table'[Month])
//Calculate the last day of the stored month (EOMONTH wrapper is included just in case your table does not include transactions up to the last day of the month. Would not be needed if you are using a date dimension)
Var D = EOMONTH(Calculate(Max('Table'[Transaction Date]),all('Table'),'Table'[Month]=M),0)
//Use EOMONTH to calculate a -6 month offset date
Var DOffset = EOMONTH(D,-6)
//Count transaction ID for customer where in the selected month
Var TCount = Calculate(Count('table'[Transaction ID]),all('table'),'table'[Month] = M,'Table'[Customer ID]= CID)
//Return variables
RETURN
//Check if TCount is greater than 0
if(TCount > 0,
//If TCount is greater than 0 then return "Include" for any records where the [Transaction Date] is between D and DOffset dates else return "Exclude"
if('Table'[Transaction Date] <= D && 'Table'[Transaction Date]>= Date(Year(DOffset),Month(DOffset),1),"Include","Exclude"),
//If TCount <=0 then returns "Exclude". Filter measure to "Include" only.
"Exclude")
zausten
3 years agoHelper I
thanks halfglassdarkly
I did have an earlier version using count of transactions for current month then do xyz. Ill see if can adapt. Appreciate lack of data visibility, but effectively a flat transaction table with a date dimension.