Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jorismo
Helper II
Helper II

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:

Screenshot_2.png

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

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@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.

Capture.PNG

 

View solution in original post

4 REPLIES 4
Eric_Zhang
Employee
Employee

@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.

Capture.PNG

 

Hi all,

 

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

 

Thanks

Omega
Impactful Individual
Impactful 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
Impactful Individual
Impactful Individual

Create a relatationg between the two tables using name. Then create a measure:

 

Total Devices = countA(Devices)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.