DAX syntax - Countrows using allexcept filter

Question

I am using three different measures in the same visual (clustered column chart), but in one of the measures I want to leave out the filter which is used in the other measures. That's why I can't use filter on visual because in one of the measures I don't want this to be used.

I am counting rows, a specific number in 'x_channel' column, but I only want to count rows that 'does not contain "3-"' from column "associate.name" in the same table (TICKET).

How do I add this filter in the following syntax:

E-post = CALCULATE(COUNTROWS(TICKET), TICKET[x_channel]=2, USERELATIONSHIP(DIM_DATO[Opprettet], TICKET[Created]))

I think the syntax should be something like this:

E-post = CALCULATE(COUNTROWS(TICKET), TICKET[x_channel]=2 && ALLEXCEPT(TICKET, TICKET[ASSOCIATE.name]="3-"), USERELATIONSHIP(DIM_DATO[Opprettet], TICKET[Created]))

Thank you!

Answer 1

Usage of ALLEXCEPT is totally different. According to the docs and dax.guide

ALLEXCEPT - Returns all the rows in a table except for those rows that are affected by the specified column filters.

So with this function, you can manipulate filter context to remove all filters from the given table but still keep filters from the column provided to ALLEXCEPT function.

The syntax would be like

Measure = 
   CALCULATE(
      COUNTROWS( TABLE1 ),
      ALLEXCEPT( TABLE2, TABLE2[ColumnName] )
   )

For your case, try this one:

E-post = 
   CALCULATE(
      COUNTROWS( TICKET ), 
      TICKET[x_channel] = 2,
      TICKET[ASSOCIATE.name] <> "3-", 
      USERELATIONSHIP( DIM_DATO[Opprettet], TICKET[Created] )
   )

or you can use FILTER with ALL and then COUNTROWS as follows

E-post = 
   CALCULATE(
      COUNTROWS(, 
         FILTER(
            ALL( TICKET ),
            TICKET[x_channel] = 2 && TICKET[ASSOCIATE.name] <> "3-"
         )
      ),
      USERELATIONSHIP( DIM_DATO[Opprettet], TICKET[Created] )
   )

In fact, the FILTER with ALL is used under the hood in the first scenario.