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...
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.

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?

 

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

Trosa_220568 hey, will be back to desk in few minutes. Do you want to do a quick zoom?