GROUPBY with Filter

[naddi]

Hi all, 

I hope that you can help me with my Power BI problem. This is my first post, so hopefully I'm doing everything right. Unfortunately I cannot upload my sample data into a cloud at work. 

I have a table ("Tests") where I have the same person ("ID") over time ("Quarter") in different groups ("Group1" and "Group2") and different test results ("Result"). 

I want to know how many person per group have in a selected time period at least one negative test result (Result = 1) and how many have all test results positive (Result = 0). 

Example Table "Tests"

Test_ID	Quarter	ID	Group1	Group2	Result
1	1	1	A	X	0
2	1	2	A	Y	1
3	1	3	B	Z	0
4	1	4	B	Y	0
5	1	5	C	X	1
6	2	1	A	X	0
7	2	3	B	Z	0
8	2	4	B	Y	1
9	2	5	C	X	1
10	2	6	C	Z	1
11	2	7	D	Y	0
12	3	1	A	X	0
13	3	3	B	Z	1
14	3	4	B	Y	0
15	3	5	C	X	0
16	3	6	C	Z	0
17	3	7	B	Y	1
18	3	7	D	Y	0
19	3	8	B	X	0
20	3	8	D	X	1

I have a second table "Quarter_dist" with one row per quarter, which I use for a date slicer. It has a relation 1:n to table "Tests". 

I tried to create a table, which filters table "Tests" on the selected quarters, groups by "ID", "Group1" and "Group2" and calculates the maximum of "Result". But the filter on the selected quarters ist not working. Has anyone an idea how to solve this? 

ID_distinct = 
VAR _Selectedtime =
    VALUES ( Quarter_dist[Quarter] )  
RETURN
GROUPBY (
        FILTER (
            Tests; 
            Tests[Quarter] IN _Selectedtime
        );
        Tests[ID];
        Tests[Group1];
        Tests[Group2];
        "count_ID"; 
        COUNTX (
            CURRENTGROUP ();
            Tests[ID]
        );
        "max_Ergebnis"; 
        MAXX ( 
            CURRENTGROUP (); 
            Tests[Result]
        )
)

This ist the result in a matrix: 

Expectation: Ignoring quarter "1", when selecting quarter "2" and "3".

Thanks in advance!

[naddi]

Thanks @abusen333 and @amitchandak  for you answers! I think it is not exactly what I am looking for. 

What I want: Aggregated data per test person for the selected quarters. One test person is identified by ID, Group1 and Group2 (and in the real data about five more groups).  The test person is tested in one or more quarters. I need aggregated information about the test person for the selected quarters, for example the maximum of "Result" to get 

- "0" if "Result" is "0" in all selected quarters

- "1" if "Result" is "1" in at least one of the selected quarter

Then I could create another column, which would be for example "passed" if max_Result = 1, "not passed" if max_Result = 0. 

The same I need for other columns aswell (not in the example).

[amitchandak]

@naddi , try the solution from @abusen333 .

But I think you need something like

one failure =

countx(filter(summarize(Table, Table[ID],"_1" ,countrows(Table)
,"_2" ,countrows(filter(Table, Table[Result] =0))
,"_3" ,countrows(filter(Table, Table[Result] =1))
)
not(isblank(_3))))

All Pass =

countx(filter(summarize(Table, Table[ID],"_1" ,countrows(Table)
,"_2" ,countrows(filter(Table, Table[Result] =0))
,"_3" ,countrows(filter(Table, Table[Result] =1))
)
[_1] = [_2] ))

[abusen333]

Hi ,

I can be simlpy achived relay on the visual filter context of the simple count of IDs .

Result

Data Model

Please let me know if this what you are looking for