Forum Discussion

Trosa_220568's avatar
Trosa_220568
Regular Visitor
4 years ago
Solved

COUNT

Hi,

I have a table of distinct Account records, and I want a measure that counts how many Activities each Account have in a table of Activities. If an Account doesn't have any Activities, show Blank, otherwise show the number of Activities. I will basically count how many times the AccountId appears in the Activity table.

 

Account A3
Account B2
Account C 
Account D5
Account E 

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Trosa_220568 ,

    There are two ways, please choose:

    First way: select "Show items with no data"

     

    Second way: adjust your DAX formula

    NO.Activities =
IF (
    HASONEVALUE ( Accounts[ID] ),
    IF (
        SELECTEDVALUE ( Accounts[ID] ) IN VALUES ( Activities[Account ID] ),
        COUNTX ( Activities, Activities[Account ID] ),
        ""
    ),
    COUNTX ( Activities, Activities[Account ID] )
)

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Trosa_220568 need a little bit more info about your model 🙂 Are the tables related? In what way? Where do you want to present this result? as a new column? In a visual?
    Can you share a sample file and answer the above questions and I could send back the code

    • Trosa_220568's avatar
      Trosa_220568
      Regular Visitor
      4 years ago

      Hi,

      Tables related from Accounts to Activities

      Table data:

      Accounts:

      Activities:

       

      I want a count for each account on the left hand side of how many times the Account ID appears in the Activities table. I currently have this measure: 

      No. Activities = COUNTX(Acitivities,Acitivities[Account ID])

      That filters down the table to only show Accounts with an Activity. I need a measure that will show ALL the Accounts regardless if they have an Activity or not. Show blank if there are no activities.

       

      Does this make more sense?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Trosa_220568 ,

    There are two ways, please choose:

    First way: select "Show items with no data"

     

    Second way: adjust your DAX formula

    NO.Activities =
IF (
    HASONEVALUE ( Accounts[ID] ),
    IF (
        SELECTEDVALUE ( Accounts[ID] ) IN VALUES ( Activities[Account ID] ),
        COUNTX ( Activities, Activities[Account ID] ),
        ""
    ),
    COUNTX ( Activities, Activities[Account ID] )
)

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.