Forum Discussion

jay13's avatar
jay13
Regular Visitor
1 year ago
Solved

Countrows with filters

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 cou...
Need Help
  • Gabry's avatar
    Gabry
    1 year ago

    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
Gabry's avatar
Gabry
Super User
1 year ago

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

jay13's avatar
jay13
Regular Visitor
1 year ago

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's avatar
    Gabry
    Super User
    1 year ago

    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's avatar
      jay13
      Regular Visitor
      1 year ago

      Thanks so much for the help!