Count distinct rows from other table + value

[jorismo]

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

[Eric_Zhang · Accepted solution]

@jorismo

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.

[jorismo]

Hi all,

many thanks for your help, it's working like you described!

Thanks

[Omega]

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)