Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 A | 3 |
Account B | 2 |
Account C | |
Account D | 5 |
Account E |
Thanks
Solved! Go to Solution.
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.
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 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
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:
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?
@Trosa_220568 hey, will be back to desk in few minutes. Do you want to do a quick zoom?
User | Count |
---|---|
109 | |
101 | |
100 | |
69 | |
34 |
User | Count |
---|---|
146 | |
141 | |
116 | |
98 | |
52 |