Need help in advance DAX

[Anonymous]

Hi experts, I'm newbie in this field. I stucked with an DAX formular for 1 week :((

Here is my context:

- I have a table that showed login events for each user

- 1 user can login many time in 1 day. (ex: User A,  login 6 times in 6/5/2020, equal 6 data  rows)

I need to caculate how many users that login more than 4 days in a specific week.

Please adviced me which DAX should be usefull in this case.

I attached the sample data and require output in below. Please kindly take a look.

https://docs.google.com/spreadsheets/d/1i0B9JXyJoR7VN9wmvepWLJvdp6aqtTC1X8WGs7aniUY/edit#gid=0

Thanks in advance.

[mwegener · Accepted solution]

Hi @Anonymous ,

take a look at the attached solution.

[Ashish_Mathur]

Hi,

Please share another 2 column Calendar table with Date in column1 and week number in column2.  I am asking for this because i do not know whether you would want to follow "week" the way the common man understand it or "ISO weeks".  So please prepeare that 2 column table.

[Greg_Deckler]

@Anonymous  - 

First, make sure that you have a Year column Year=YEAR([Date]). Also make sure that you have a Week column, Week = WEEKNUM([Date]).

You can then create a measure like the following:

Measure = 
  VAR __User = MAX('Table'[User])
  VAR __Year = YEAR(MAX('Table'[Date]))
  VAR __Week = WEEKNUM(MAX('Table'[Week]))
  VAR __Table = SUMMARIZE('Table',[User],[Year],[Week],[Day],"Count",COUNTROWS('Table'))
  VAR __Count = COUNTROWS(FILTER(__Table,[User]=__User && [Year]=__Year && [Week]=__Week))
RETURN
  IF(__Count > 4,1,0)

[Anonymous]

@Greg_Deckler 

Really apreciate your solution.

I'm trying to follow your measure, but it seem not correct for me.

Please kindly take a look at the picture in below

If I did anything wrong, please help me to point out.

Thanks alot Mr @Greg_Deckler 

[Greg_Deckler]

@Anonymous - OK, try ditching the filter on the user's name in the __Count variable. I think that might do it. In fact, do this instead:

VAR __Count = COUNTROWS(FILTER('Table', [Year] = __Year && [Week] = __Week && [Count] > 4))

Sorry, I was thinking the visual was going to be laid out a different way.