Need a Dax calculated column for the following problem.

[Iamanonymoususr]

Hi,

I am trying to create a calculted column for my users, please refer to the sample data, If you can help would be very grateful. My objective is remove any user inactive for four days/BYE from visuals. My options for visual are limited, I "cannot use table or matrix"  hence using a measure is out of question. I would prefer Dax calculated column over Query, but If you can leave both justify which is better would be welcome as well. I have been at it for long time, have tried many different solutions got close to solving the problem using countrows but my problem didn't get solve as if a user comes back online after 2 days of inactivity my column still counted the data in series after they went offline again. If they had been offline twice it counted 2 then blank since they came back online then again offline but "instead of showing 1 again it showed 3". My data base is 50,000 rows, additon of 1,700 rows everyday. Hope this problem present a challenge for you. Please help I'm in dire need. I have tried more than 10 solution for similar issues, would appreciate an attached pbix file than links for blogs and tutorials. 

Kudos Awaiting on every solution you give after solving this, for any user problem in the community because I'll follow you here and probably learn from whoever can solve this.

Good day!!!

USER ID	LAST SENT DATE	MESSAGE SENT
@amitchandak	07/01/2022 (dd/mm/yyyy)(shortdate)only date available.	HI
@lbendlin	07/01/2022	BYE
@Greg_Deckler	07/01/2022	BYE
@tamerj1	07/01/2022	HI
@FreemanZ	07/01/2022	HI
@amitchandak	10/01/2022	HI
@lbendlin	10/01/2022	BYE
@lbendlin @Greg_Deckler	10/01/2022	BYE
@tamerj1	10/01/2022	HI
@FreemanZ	10/01/2022	BYE
@Greg_Deckler@amitchandak	15/01/2022	BYE
@tamerj1 @lbendlin	15/01/2022	BYE
@Greg_Deckler	15/01/2022	HI
@tamerj1	15/01/2022	HI
@FreemanZ	15/01/2022	BYE
@amitchandak	17/01/2022	HI
@lbendlin	17/01/2022	HI
@Greg_Deckler	17/01/2022	BYE
@tamerj1	17/01/2022	BYE
@FreemanZ	17/01/2022	BYE

The result I want :

USER ID	LAST SENT DATE	MESSAGE SENT	SERIES(any name)
@amitchandak	07/01/2022	HI	0 or BLANK()
@amitchandak	10/01/2022	HI	0 or BLANK()
@amitchandak	15/01/2022	BYE	1
@amitchandak	17/01/2022	HI	0 or BLANK()
@lbendlin	07/01/2022	BYE	1
@lbendlin	10/01/2022	BYE	2
@lbendlin	15/01/2022	BYE	3
@lbendlin	17/01/2022	HI	0 or BLANK()
@Greg_Deckler	07/01/2022	HI	1
@Greg_Deckler	10/01/2022	HI	2
@Greg_Deckler	15/01/2022	HI	0 or BLANK()
@Greg_Deckler	17/01/2022	HI	1
@tamerj1	07/01/2022	HI	0 or BLANK()
@tamerj1	10/01/2022	HI	0 or BLANK()
@tamerj1	15/01/2022	HI	0 or BLANK()
@tamerj1	17/01/2022	BYE	1
@FreemanZ	07/01/2022	HI	0 or BLANK()
@FreemanZ	10/01/2022	BYE	1
@FreemanZ	15/01/2022	BYE	2
@FreemanZ	17/01/2022	BYE	3

[tamerj1]

Hi @Iamanonymoususr 

The date is a little missed up which makes it confusing. Some rows have user names. At least this how I see it on my phone. 

[Iamanonymoususr]

Each row has only one username sir and dates are missed up because of holiday's, server maintainance for which we can't download the data, etc. But dates are irregular, working days of the server is 6 days a week, starting monday till saturday, Sundays data we'are ignoring as it's more difficult to fetch and source is different.

[tamerj1]

 

@Iamanonymoususr 

This is what I see

 

[Iamanonymoususr]

Sir thank you for sparing your time, I think it happened due to network issues, every cell having two usernames, remove the first user name and that's the real data, I hope it clear. If not let me know I'll make a table again put it here for reference.

[tamerj1 · Accepted solution]

@Iamanonymoususr 

First create the following calcuted column

GROUP =
VAR Rank1 =
RANKX (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[USER ID] ) ),
'Table'[LAST SENT DATE]
)
VAR Rank2 =
RANKX (
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[USER ID], 'Table'[MESSAGE SENT] )
),
'Table'[LAST SENT DATE]
)
RETURN
Rank1 - Rank2

then the SERIES calculated column would be

SERIES =
VAR CurrentMessage = 'Table'[MESSAGE SENT]
VAR CrrentDate = 'Table'[LAST SENT DATE]
RETURN
IF (
CurrentMessage = "BYE",
COUNTROWS (
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[USER ID], 'Table'[MESSAGE SENT], 'Table'[GROUP] ),
'Table'[LAST SENT DATE] <= CrrentDate
)
)
)

[Iamanonymoususr]

Hi, sir apologies for the delayed response I'm working on with the solution you have provided, it's solving the problem for my MESSAGE = BYE, but the series is all generating for MESSAGE SENT = HI, If I use the column as filter it filters out both, which I don't want. I only want to filterout BYE for series >= 7. If you look at the post I requested 0/Blank()/null for "HI". Any suggestions how to work around it?


But otherwise whatever you have done is just amazing, it's fast as well, my Power BI doesn't take a lot of time or runs out of memory.

[tamerj1]

@Iamanonymoususr 

I modified the code of SERIES in my original reply

[Iamanonymoususr]

Thank you for investing your valuable time on this, really appreciate it. This is the best possible solution for a problem like this. It's working on my main data, I checked this morning.