Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
naddi
Regular Visitor

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: 

naddi_0-1654179443515.png

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

 

Thanks in advance!

3 REPLIES 3
naddi
Regular Visitor

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
Super User
Super User

@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
Frequent Visitor

Hi ,

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

Result

abusen333_1-1654181524951.png

 

Data Model

abusen333_2-1654181575640.png

abusen333_3-1654181609277.png

abusen333_4-1654181632721.png

 

Please let me know if this what you are looking for 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors