topic Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table? in DAX Commands and Tips

How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

gingerclaire — Wed, 06 Mar 2024 10:17:29 GMT

I have a model which has four different tables for different kinds of sales activities (how our database is set up and I can't merge them as the four tables are different). They are all linked to a table called 'accounts' which holds all of the info on the account that the activity happened to.

I already have a measure which totals up all the activities that happened.

BUT multiple activities can happen at one account.

How do I take my measure which basically counts rows in the activity tables and get it to dedupe by or give a distinct count of 'accountid'?

Or do I need to make a virtual table? and if so, how?

EDITED TO ADD MORE INFO:

Table 1: Appointments

Table 2: CPD

Table 3: FOBs

Table 4: Q4

The above are all types of sales activities. They are all linked to (among many others):

Table 5: Account info

Table 6: Date

I have a measure that counts the number of activities (counting rows) in each of the first four tables. (Total appointments) (Total FOBs) (Total CPD) (Total Q4)

I have a measure that then adds all of these together (Total activities) which gives me the total number of these activities. But i want to identify the total distinct number of activities where these activities have taken place (as one account may have had many activities).

Is there a way to do it using these existing measures?

Or is there a way to get a list of account IDs from Tables, 1,2,3,4 and distinct count that?

Thank you 🙂

Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

gmsamborn — Tue, 05 Mar 2024 18:02:24 GMT

I would like to help but I'm a little unclear about your requirements.

Can you show the following?

1) Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file would be best.

Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

Anonymous — Wed, 06 Mar 2024 02:37:41 GMT

Hi gingerclaire ,

My understanding is that you can use the DISTINCTCOUNT function if you want to get a different count of "accountid" using a measure in Power BI.

This function counts the number of non-duplicate values in a column. If you want to know more about it, please see:

DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn

Please try:

Distinct Account Activities = DISTINCTCOUNT('accounts'[accountid])

I would appreciate it if you could provide me with sample data that does not contain your private information.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

gingerclaire — Wed, 06 Mar 2024 11:01:02 GMT

I have added more info above - I can't include sample data due to company policy.

I can do a distinct count in one table - the problem is i need to be able to make a list from tables 1,2,3,4 and then do the distinct count of a column in table 5. PLease see edited question for more info 🙂

Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

gingerclaire — Wed, 06 Mar 2024 11:09:53 GMT

I have added more info above to my edited question - I can't include sample data sadly due to company policy.

I want to end up with a distinct count of accountids (table 5) where there had been ANY activities (tables 1-4).

I have a measure to count the activities but i do not know how to narrow that down to count the accountids (there are more than one activity at each account)

I can do a distinct count in one table - the problem is i need to be able to make a list from tables 1,2,3,4 and then do the distinct count of a column in table 5.

PLease see edited question for more info 🙂

Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

Anonymous — Thu, 07 Mar 2024 05:56:50 GMT

Hi gingerclaire ,

Thank you for the more detailed description, it is very helpful.

Please follow these steps:

1. Here is the raw data and model relationship diagram I created.

Accounts table:

Appointments table:

CPD table:

FOBs table:

Q4 table:

Modeling relationship:

2. Please try:

Measure = VAR __table1 = VALUES('Appointments'[Activity]) VAR __table2 = VALUES('CPD'[Activity]) VAR __table3 = VALUES('FOBs'[Activity]) VAR __table4 = VALUES('Q4'[Activity]) VAR __union_tables = UNION(__table1,__table2,__table3,__table4) VAR __result = COUNTROWS(DISTINCT(__union_tables)) RETURN __result

This should now fulfill your needs, if you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

Re: How can a distinctcount or de-dupe a measure? Or hwo do i make a virtual table?

gingerclaire — Tue, 12 Mar 2024 13:03:34 GMT

I have just set this up and am running some checks but it seems to be working!!

I wanted to be able to count the number of accounts that had AN activity at it, so I changed the DAX formula slightly:

Measure = VAR __table1 = VALUES('Appointments'[AccountID]) VAR __table2 = VALUES('CPD'[AccountID]) VAR __table3 = VALUES('FOBs'[AccountID]) VAR __table4 = VALUES('Q4'[AccountID]) VAR __union_tables = UNION(__table1,__table2,__table3,__table4) VAR __result = COUNTROWS(DISTINCT(__union_tables)) RETURN __result

I will let you know (and mark as correct) if the solution works and passes my manual check 🙂