Forum Discussion
Solved
Formulae for repeat usage across rows
Hi, I need to find users who have been present in the data in last 3 month and twice in last three month (incl. current month) like shown in the examples below respectively. Column 1 and 2 are in...
vibhoryadav23 OK, this should do it. PBIX is attached below signature.
Column = VAR __User = [Users] VAR __Period = [Period] VAR __PeriodDate = DATE(LEFT(__Period,4), RIGHT(__Period, 2), 1) VAR __EndDate = EOMONTH(__PeriodDate, -2) VAR __EndDateNum = YEAR(__EndDate) * 100 + MONTH(__EndDate) VAR __Count = COUNTROWS( FILTER( 'Table2', [Users] = __User && [Period] <= __Period && [Period] >= __EndDateNum ) ) VAR __Result = IF( __Count >= 3, 1, 0 ) RETURN __Result Column 2 = VAR __User = [Users] VAR __Period = [Period] VAR __PeriodDate = DATE(LEFT(__Period,4), RIGHT(__Period, 2), 1) VAR __EndDate = EOMONTH(__PeriodDate, -2) VAR __EndDateNum = YEAR(__EndDate) * 100 + MONTH(__EndDate) VAR __Count = COUNTROWS( FILTER( 'Table2', [Users] = __User && [Period] <= __Period && [Period] >= __EndDateNum ) ) VAR __Result = IF( __Count >= 2, 1, 0 ) RETURN __Result
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a measure.
WINDOW function (DAX) - DAX | Microsoft Learn
Present all in last 3 months: =
IF (
COUNTROWS (
WINDOW (
-2,
REL,
0,
REL,
SUMMARIZE ( ALL ( Data ), Months[Months], Users[Users] ),
ORDERBY ( Months[Months], ASC ),
,
PARTITIONBY ( Users[Users] )
)
) >= 3,
1,
0
)
Present 2 in last 3 months: =
IF (
COUNTROWS (
WINDOW (
-2,
REL,
0,
REL,
SUMMARIZE ( ALL ( Data ), Months[Months], Users[Users] ),
ORDERBY ( Months[Months], ASC ),
,
PARTITIONBY ( Users[Users] )
)
) >= 2,
1,
0
)
Jihwan_Kim Thanks for the effort. This doesnt work unfortunately.
I have added an example of a user in the original post for more clarity.