Calculate a measure based on 2 conditions in a group by

[Anonymous]

Greeting lovely members 

here is a sample table that i choose for my problem : 

Student ID	Status	Year
1	Refused	2021
1	Refused	2021
1	Accepted	2020
2	Accepted	2021
2	Refused	2021
2	Refused	2021
3	Refused	2021
3	Refused	2021
3	Refused	2021
4	Refused	2020
5	Accepted	2021
5	Refused	2021

 im trying to calculate the number of student who got rejected in all their status starting from 2021 . 

So my input will be 2 : Student number 1 and student number 3 

Anyone can help me with this trick ? thanks in advance.

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

countx(filter(summarize(Table, Table[Student Id], "_1", countx(filter(Table[Year] =2021), [Student ID]), "_2", countx(filter(Table[Year] =2021 && [Status] ="Refused" ), [Student ID])), [_1] =[_2]),[Student Id])

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Please try the following formula to creata a measure:

Flag =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Year] >= 2021
                && 'Table'[Student ID] = MAX ( 'Table'[Student ID] )
        )
    ) + 0
VAR _a =
    COUNTX (
        FILTER (
            'Table',
            'Table'[Student ID] = MAX ( 'Table'[Student ID] )
                && 'Table'[Status] = "Refused"
        ),
        _count
    )
RETURN
    IF ( _count = _a && _count <> 0, 1, 0 )

Then apply it to filter pane, here is the final output:

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

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

countx(filter(summarize(Table, Table[Student Id], "_1", countx(filter(Table[Year] =2021), [Student ID]), "_2", countx(filter(Table[Year] =2021 && [Status] ="Refused" ), [Student ID])), [_1] =[_2]),[Student Id])