Countrows with filters

[jay13]

I am trying to count the rows for an ID where they only have a specific code.

Example: If I am looking to count the IDs that only have code D how would I count just that row. In this case the count would be 1 because only ID 3 has code D only.

ID	code
1	M
1	D
1	L
2	D
2	M
3	D

[Gabry · Accepted solution]

Hello @jay13 

this is your measure

ContaSoloD =

CALCULATE (

    DISTINCTCOUNT ( Tabella[ID] ),

    FILTER (

        VALUES ( Tabella[ID] ),

        CALCULATE ( COUNTROWS ( FILTER ( Tabella, Tabella[code] <> "D" ) ) ) = 0

    )

)

let me know if it works

[SachinNandanwar]

Just incase if you want to return ID's that only have one instance of "D"

Table_1 = 
VAR _Table = 
    SUMMARIZE(
        'Table',
        'Table'[ID],
        "Codes", DISTINCTCOUNT( ( 'Table'[code] )),
        "Codes_D",COUNTROWS( FILTER( 'Table', 'Table'[code] = "D" ))
    )

RETURN  FILTER(_Table,[Codes]=[Codes_D])

[Gabry · Accepted solution]

Hello @jay13 

this is your measure

ContaSoloD =

CALCULATE (

    DISTINCTCOUNT ( Tabella[ID] ),

    FILTER (

        VALUES ( Tabella[ID] ),

        CALCULATE ( COUNTROWS ( FILTER ( Tabella, Tabella[code] <> "D" ) ) ) = 0

    )

)

let me know if it works

[jay13]

thank you for the reply. This seemed to work at first but if I add additional rows it is still only counting one?

Example: It should count 3 since ID's 3,4 and 5 have code D only. 

ID	code
1	M
1	D
1	L
2	D
2	M
3	D
4	D
5	D

[ThxAlot · Accepted solution]

Simple enough,

[jay13]

Thank you @ThxAlot this one works as well!

[Gabry]

You're right. I changed the previous post with the updated formula. Let me know

[jay13]

Thanks @Gabry ! Are you able to explain how this is working? I am understanding pieces of it but when we get to the last filter where its <> "D" then =0 I am getting a little lost. I really appriciate the help on this one. Thanks again!

[Gabry]

Yeah, sure, I can try. You can use the DAX query view to help debug the code.

This part of the code:
FILTER (
VALUES ( Tabella[ID] ),
CALCULATE ( COUNTROWS ( FILTER ( Tabella, Tabella[code] <> "D" ) ) ) = 0
)

filters out all the IDs that have a code different from "D". It essentially says to keep only the rows where the count is 0.

Then, you just count the IDs in this filtered table with:

DISTINCTCOUNT ( Tabella[ID] )

and you'll get the number of IDs.

😉

[jay13]

Thanks so much for the help!