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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.