Multiple countifs DAX

Hi,

I want to make a multiple countifs statement in DAX.

The dataframe looks like this:

Routing-port TEX invoice (text format) countifs result

XXXX-1 2 TEXT123 1

XXXX-1 1 TEXT123

XXXX-1 2 TEXT124 1

The count should be that each routing port, TEX should be 2 and invoice should be unqiue. Hence, the countifs formula in Excel looks like this:=COUNTIFS(T:T;[@[Routing port/port]];CY:CY;[@[Invoice Numbers]]). I manually filtered TEX= 2.

I made this formula, but that doesn´t work.

Weight_factor =

CALCULATE(

COUNT([Routing port/port]),

FILTER(

'source data',

'source data'[Invoice Numbers],

'source data'[TEX]=2,

)

v-zhangti

4 years ago

Hi, Spartanos

Is your Routing-port the same value, or a different one? SELECTEDVALUE is placed in Measure. Whether your Routing-port is the same value or a different value, there is a solution.

Routing-port is the same value:

Column:

Count 1 =
IF (
    [TEX] = 2,
    CALCULATE (
        COUNT ( 'Table'[Routing-port] ),
        FILTER ( 'Table', [TEX] = 2 && [invoice] = EARLIER ( 'Table'[invoice] ) )
    ),
    BLANK ()
)

Measure:

Measure =
IF (
    SELECTEDVALUE ( 'Table'[TEX] ) = 2,
    CALCULATE (
        COUNT ( 'Table'[Routing-port] ),
        FILTER ( 'Table', [TEX] = 2 && [invoice] = SELECTEDVALUE ( 'Table'[invoice] ) )
    ),
    BLANK ()
)

Routing-port is a different value.

Column:

Count 2 =
CALCULATE (
    COUNT ( 'Table 2'[Routing-port] ),
    FILTER (
        'Table 2',
        [TEX] = 2
            && [invoice] = EARLIER ( 'Table 2'[invoice] )
            && [Routing-port] = EARLIER ( 'Table 2'[Routing-port] )
    )
)

Measure:

Measure2 =
CALCULATE (
    COUNT ( 'Table 2'[Routing-port] ),
    FILTER (
        ALL ( 'Table 2' ),
        [TEX] = 2
            && [invoice] = SELECTEDVALUE ( 'Table 2'[invoice] )
            && [Routing-port] = SELECTEDVALUE ( 'Table 2'[Routing-port] )
    )
)