slicer date not working properly in sum measure

[MostafaGamal]

Hi all, I have a table that contain mutiple rows for every user (mail) and to calculate the percentage of participation i want to count the no. of Entries for every user but this value should not exceed " 2 " so i create the following measure to count the no. of Entries for every ueser in the first 2 rows ( 1 entry = 1 row ) in a selected range of date by a slicer 

BOS # = 
VAR tab =
    SUMMARIZE (
        Sheet1,
        Sheet1[EntriesTime].[Date],
        Sheet1[Index],
        Sheet1[EntriesCount ],Sheet1[Mail],
        "count", CALCULATE (
            COUNT ( Sheet1[Mail] ),
            FILTER (
                ALLEXCEPT ( Sheet1, Sheet1[Mail] ),
                'Sheet1'[Index] <= MAX ( 'Sheet1'[Index] )
            )
        )
    )
RETURN
    SUMX ( FILTER ( tab, [count] <= 2 ), [EntriesCount ] ) 

and it works fine but when i change the range of date in the slicer the value i got from the meaure is " blank " ( the measure works fine when i choose the range date in July when i choose the range date august it gives my blank ) 

Please advise how to slove this issue ?? 

here is an example of the data i expect from the meaure 

here is a sample of the data i use in my table 

Index	Department	Line	Name	Mail	EntriesCount	EntriesTime
1	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/1/2020
3	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/14/2020
4	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/15/2020
5	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/16/2020
6	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/17/2020
7	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	1	7/13/2020
8	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	7/16/2020
9	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	7/17/2020
10	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	7/18/2020
11	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	7/12/2020
12	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	7/13/2020
13	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	7/14/2020
14	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	7/15/2020
16	FC		Hesham Barakat	barakat.hs@pg.com	1	7/13/2020
17	FC		Hesham Barakat	barakat.hs@pg.com	1	7/17/2020
18	FC		Hesham Barakat	barakat.hs@pg.com	0	7/16/2020
19	FC		Hesham Barakat	barakat.hs@pg.com	0	7/22/2020
20	FC		Sameh Mohammedy	sameh.ms@pg.com	1	7/12/2020
21	FC		Sameh Mohammedy	sameh.ms@pg.com	0	7/13/2020
22	FC		Sameh Mohammedy	sameh.ms@pg.com	0	7/31/2020
23	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	8/1/2020
25	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	8/14/2020
26	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	8/15/2020
27	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	8/16/2020
28	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	8/17/2020
29	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	1	8/13/2020
30	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	1	8/16/2020
31	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	8/17/2020
32	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	8/18/2020
33	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	7/12/2020
34	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	8/13/2020
35	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	8/14/2020
36	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	8/15/2020
37	FC		Hesham Barakat	barakat.hs@pg.com	1	8/1/2020
38	FC		Hesham Barakat	barakat.hs@pg.com	1	8/13/2020
40	FC		Hesham Barakat	barakat.hs@pg.com	0	8/16/2020
41	FC		Hesham Barakat	barakat.hs@pg.com	0	8/22/2020
42	FC		Sameh Mohammedy	sameh.ms@pg.com	1	8/12/2020
43	FC		Sameh Mohammedy	sameh.ms@pg.com	0	8/13/2020
44	FC		Sameh Mohammedy	sameh.ms@pg.com	0	8/31/2020

[Fowmy]

@MostafaGamal 

Hi Mostafa,

As per your Measure, there is no data for August that fall <= 2, Please check the attached file you are getting the output as expected.

https://1drv.ms/u/s!AmoScH5srsIYgYIqPIJEhhuCWtoZSw?e=uI6qHA

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[MostafaGamal]

@Fowmy  you can see in my table that there is values in the coumn " No.OfEntries " in august, so i dont understand what you mean by  " As per your Measure, there is no data for August that fall <= 2 " .. Please Clarify more 

Kindly note that the link you provide is not working, i cant open it and download the file 

[Fowmy]

@MostafaGamal 

Because you SUMMARIZE the table first then add the count that is below or equal 2, your table doesn't have data that qualify.
If you change the 2 to a higher number like 10, it will work,

 SUMX ( FILTER ( tab, [count] <= 2 ), [EntriesCount ] ) 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[MostafaGamal]

@Fowmy  ok i got it now, so to achieve what i want to do which is " count the values in the first 2 rows for every user in a selected range of date " how to do that? what you suggest ? 

the link in the attched in your first comment i cant open it, please share it again 

[Fowmy]

@MostafaGamal 

Try this measure:

https://1drv.ms/u/s!AmoScH5srsIYgYIqPIJEhhuCWtoZSw?e=QUihP0

Measure = 
SUMX(
FILTER(SUMMARIZE(Sheet1,Sheet1[Mail],"Count",SUM(Sheet1[EntriesCount ])),[Count]<=2),
[Count])

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn