CODEWITHSUNDEEP
daxpowerbi-desktop
nsr
nsr

Reputation: 107

DAX formula to count group by with filter

I have a table like below in power BI with two columns Category and subcategory. I m trying to get the count of subcategory="S2" for each category into a calculated column (like in S2_count). 

Category    Subcategory S2_count
  A           S1        1
  A           S2        1
  A           S1        1
  B           S1        2
  B           S3        2
  B           S2        2
  B           S2        2
  C           S2        2
  C           S3        2
  C           S2        2

Is there a way using the DAX to get this ? I tried the below formula but no clue how to apply both filter and group by

s2_count = 
            CALCULATE(
              COUNT(Test01[subcategory]),
                   GROUPBY(Test01,Test01[subcategory]))

Upvotes: 3

Views: 18057

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40254

You can also do this using CALCULATE.

s2_count =
CALCULATE( COUNTROWS( Test01 ),
    Test01[Subcategory] = "S2",
    ALLEXCEPT( Test01, Test01[Category] )
)

The ALLEXCEPT function removes any of the row context except for the Category.

Note: If there are no other columns in your table, you don't need the ALLEXCEPT argument and you can just use this instead:

s2_count = CALCULATE( COUNTROWS( Test01 ), Test01[Subcategory] = "S2" )

If you do have other columns though, they are passed from row context to filter context along with the Category and you won't get the right result.

Upvotes: 1

Marco Vos
Marco Vos

Reputation: 2967

Thy this:

s2_count =
COUNTROWS (
    FILTER (
        'Test01',
        'Test01'[Category] = EARLIER ( 'Test01'[Category] )
            && 'Test01'[Subcategory] = "S2"
    )
)

The EARLIER Function will return 'Test01'[Category] in its previous filtercontext, which is the rowcontext.

enter image description here

Upvotes: 1

Related Questions