IF Statement FILTER (At least One field)

[Anonymous]

Hi, I have this table

Client	Contract	YES/NO
1	a	YES
1	b	NO
1	c	NO
2	d	NO
2	e	NO
3	f	YES
3	g	YES
3	h	NO
4	i	NO
4	j	NO

How can I COUNT the CLIENTS without YES ? In the example, my count would be = 2 (Client 2 and 4 doenst have any YES)

Thanks!

[Greg_Deckler · Accepted solution]

See if this works:

Measure = 
    CONCATENATEX(
        EXCEPT(
            DISTINCT('Table'[Client]),
            DISTINCT(SELECTCOLUMNS(FILTER('Table',[YES/NO]="YES"),"Client",[Client]))
        ),
        [Client],
        ","
    )

[PaulDBrown · Accepted solution]

@Anonymous 

To count clients without a "YES":

All Clients = Distinctcount (table [Client])

Clients with "Yes" = CALCULATE(DISTINCTCOUNT(Table [Client]),
                     FILTER( Table,
                     Table [YES/NO] = "YES"))

Clients Without "Yes" = [All Clients] - [Clients with "Yes"]

[gpiero]

@Anonymous 

Hi,

according to your data 1a <> 1b, right?

Is the following pict the logic you are looking for?

[Greg_Deckler · Accepted solution]

See if this works:

Measure = 
    CONCATENATEX(
        EXCEPT(
            DISTINCT('Table'[Client]),
            DISTINCT(SELECTCOLUMNS(FILTER('Table',[YES/NO]="YES"),"Client",[Client]))
        ),
        [Client],
        ","
    )

[Anonymous]

Hi , Thanks for your reply.

I wanted to count the Clients but your measure was useful to fix my problem.

Regards,