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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Not applicable

GROUPBY with filter conditions on columns


Is it possible to use GROUPBY with some sort of conditions on the columns?  Something along the lines of 

    'Employee Detail',
    'Employee Detail'[Cost Ctr],
    "Count of Primary", COUNTAX(CURRENTGROUP(), 'Employee Detail'[Employee Category] = "Primary Department"),
    "Count of Residual", COUNTAX(CURRENTGROUP(), 'Employee Detail'[Employee Category] = "Residual Employees")

The first column (Primary Department) works but the 2nd column (Residual Employees) just repeats the same values as the 1st column.  I have maybe a dozen of these columns I want to add with conditions.  I know we cannot use CALCULATE with GROUPBY so maybe this is just the wrong function and I should stick with SUMMARIZE or SUMMARIZECOLUMNS instead?  What is best practice for grouping data like this with conditions?  Thanks!
Super User
Super User

I think you'd need to use ADDCOLUMNS ... SUMMARIZE, like

Summary Table =
    SUMMARIZE ( 'Employee Detail', 'Employee Detail'[Cost Ctr] ),
    "Count of Primary",
        CALCULATE (
            COUNTROWS ( 'Employee Detail' ),
            'Employee Detail'[Employee Category] = "Primary Department"
    "Count of Residual",
        CALCULATE (
            COUNTROWS ( 'Employee Detail' ),
            'Employee Detail'[Employee Category] = "Residual Employees"
Super User
Super User

@Anonymous , I think you can do some calculations using Earlier



If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors