Access into college twice a week

[samoberoi]

Hi, 

I previously have managed to get the Min and Max of all the students out from all the swipes of the access card done during the day at the entrance gate, which gave me the students' first entrance and last exit from the building and i did the full count of all the students' first entry and last exit during the week or day etc. This works fine till now. Now, i have to count how many students have been into the building just three days a week. Is there any form of DAX which will help me in extracting the students who have been in just three days a week? I am really struggling with it. 
Help will be much appreciated. 

Thanks

[samoberoi]

Hi

I can't thank you enough for this help. Stay blessed.

Cheers!

[v-rzhou-msft · Accepted solution]

Hi @samoberoi ,

I think your Table should look like as below. You can add a [Date] column in your table.

Date = IF(DATEVALUE('Table'[Entrance]) = DATEVALUE('Table'[Exit]),DATEVALUE('Table'[Entrance]))

I suggest you to create a Calendar table to help calculation.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeekDay", WEEKDAY ( [Date], 2 ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 2 ) + 1,
    "WeekEnd",
        [Date] - WEEKDAY ( [Date], 2 ) + 7,
    "WeekRange",
        COMBINEVALUES (
            " - ",
            [Date] - WEEKDAY ( [Date], 2 ) + 1,
            [Date] - WEEKDAY ( [Date], 2 ) + 7
        )
)

Data model:

Measure:

Count Students in buildings three days = 
VAR _SUMMARIZE = SUMMARIZE('Table','Table'[Student ID],'Calendar'[WeekRange],"Count",CALCULATE(DISTINCTCOUNT('Table'[Date])))
RETURN
COUNTX(FILTER(_SUMMARIZE,[Count]=3),[Student ID]) + 0

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.