Forum Discussion
Count distinct rows from other table + value
Hi all,
I've got a new question.
I've got 2 data sources.
Source 1: All the names of the employees and their empl. number
Source 2: several names of employees and the devices that they have.
Now I want to create a new table wich is creating the following:
- colum 1: all the names of the employees
- colum 2: count distinct of the devices that they have, if they don't have any devices the value must be 0
I've made an example in xls to get this visualised:
Left is source 1, center: source 2 and Right is the output that I want.
I really hope that someone can give me an helping hand with this
Anonymous
In addition to Omega, for the requirement "if they don't have any devices the value must be 0", create measure as
Total Devices = IF(ISBLANK(countA(Devices[Device])),0,countA(Devices[Device]))
See the demo pbix file attched.
4 Replies
OmegaImpactful Individual
Regarding relationship, click on "Manage Relationships" button in the home ribbon. Then drag name in table 1 to name in table 2. In table 2, make sure that each row has a name and don't leave them as blank.
If still not working, please send me the PBIX file you are trying to create.
- Omega
Create a relatationg between the two tables using name. Then create a measure:
Total Devices = countA(Devices)
Eric_ZhangMicrosoft Employee
Anonymous
In addition to Omega, for the requirement "if they don't have any devices the value must be 0", create measure as
Total Devices = IF(ISBLANK(countA(Devices[Device])),0,countA(Devices[Device]))
See the demo pbix file attched.