Multiple countifs DAX

[Spartanos]

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,

)

)

[Whitewater100]

Hi:

Can you try this? Table name = Data

Result =

CALCULATE(DISTINCTCOUNT(Data[invoice(text format)]),

Data[TEX] = 2)

[Spartanos]

Hi,

Many thanks, this works. Is it also possible to make add the routing port as unique value in the formula as well?

[Whitewater100]

Hi:

Yes:

Result =

var port = Data[Routing Port]

return

CALCULATE(DISTINCTCOUNT(Data[invoice(text format)]),

Data[TEX] = 2 &&

Data[Routing Port] = port)

Can you mark as solution if this works for you? Thanks

[Spartanos]

Hi,

Am I correct that I have to put  SELECTEDVALUE in this line of code:

var port = Selectedvalue(Data[Routing Port])

So the DAX engines knows that we are looking for muliple routing ports?

[v-zhangti · Accepted solution]

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] )
    )
)

I hope you can get the results you want from it.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.