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
eburke
Helper II
Helper II

Help needed with filtering

Ok so I have a problem that I think means I've missed something really obvious and simple but I can't see what.  I have three tables:

 

Data table:

Client Name, Employee ID, Task Name, Duration, Date of Service

 

Staff table:

Staff Name, Employee ID, Staff Level

 

Role table:

Task Name, Role Name

 

Staff Name is connected to Data table through the Employee ID, Role Table is connect to Data table through Task Name.

 

I can do a visual showing the number of hours per Role based on the data table.  What I want to do is a visual showing how many employees have undertaken the different Role types by staff level.  I thought that having the three tables connected the way I do it should be a simple case of showing the roles, staff level and then doing a distinct count of the employee codes.  But when I do all the answers come up with the total number of staff, see below.  Obviously I'm missing a step here with mapping the relationship or something but I can't figure out how else to filter this.

Capture.PNG

Any help would be appreciated.  Thanks,

 

8 REPLIES 8
Phil_Seamark
Employee
Employee

Hi @eburke

 

Have you tried changing the direction of the relationships from single to bi-directional?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi, thanks for that, I tried that but it makes all the numbers disappear.

Do you have some sample data, or a cut down version of your PBIX file?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi I can't post the PBIX file even in one drive our system doesn't allow us to share anything externally.  Here's an example of the data in the table though, the names have been removed for privacy.

 

Data table:

 

Capture.PNG

 

Staff Table:

 

Capture.PNG\

 

Roles Table:

Capture.PNG

 

Relationship:

Capture.PNGThanks for your help.

Hi @eburke

 

It should work if you change the direction of your relationship.

 

I've uploaded a simple PBIX file to show an example.

 

https://1drv.ms/u/s!AtDlC2rep7a-oXfGMg93k1-CWvGT


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

Thanks for that, i can see how it works on your file.  I have set mine up exactly the same way but it still gives me a result with no numbers.

 

Capture.PNGCapture.PNGCapture.PNG

I'm stumped on this one.

hI @eburke

 

You may have some data related issues that are causing it.  If you can find out a way to get a cut down version of the PBIX file to me I can dig deeper for you.

 

Feel free to PM me and exchange email addresses if that helps.

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

Thanks for the offer, unfortunately this file has been sent to me from another source in the company and I can't access some of the data tables to be able to delete sensitive information so won't be able to send you a cut down version.  I appreciate your help, I might have to explore other options, hopefully someone in my company can help.

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.