Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

GROUPBY with Filter

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_IDQuarterIDGroup1Group2Result
111AX0
212AY1
313BZ0
414BY0
515CX1
621AX0
723BZ0
824BY1
925CX1
1026CZ1
1127DY0
1231AX0
1333BZ1
1434BY0
1535CX0
1636CZ0
1737BY1
1837DY0
1938BX0
2038DX1

 

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!

Need Help

3 Replies

  • abusen333's avatar
    abusen333
    Frequent Visitor
    4 years ago

    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 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , 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] ))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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).