Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |