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
HassanAshas
Helper V
Helper V

How to use a slicer from Table on the Many-to-one side of Dimension Table

I have three Tables in my Data Model, 

  1. Fact Table: It contains all the records, along with two columns called "Project" and "Practice"
  2. Employee Table: It contains records of all the employees. 
  3. Employee Skills Table: It contains skills against each employee (each employee can have multiple skills)

I have purposely kept Employee Skills Table and Employees Table separately because if I merge them, I will have to create a many to many relation between Fact Table and Employee table (which I believe would be bad?) 

 

My data model currently looks like this, 

 

HassanAshas_0-1682287714496.png

 

 

My problem is in my report, I need to provide the user with two slicers of Project and Practice (both these slicers are coming from Fact Table). 

Along with that, I want to provide a slicer for Skill (that will come from the Employee Skills Table) and make it interact with the other visuals in the Report. 

My report looks like this, 

 

HassanAshas_0-1682501236277.png

 

For example, if I select Mobile Development from Skills, it should show only those employees record which have the Mobile Development Skill, but it doesn't do it as of now (due to the relation and filter propagation)

 

HassanAshas_0-1682501667911.png

 

I am able to get the Bar Chart and the Total Employees visuals working using the following DAX for Total Employees (with the help of this amazing community)

 

 

Total Employees = 
CALCULATE(
    DISTINCTCOUNT(FactTable[Emp_ID]),
    TREATAS(
        DISTINCT('Employee Skills'[Emp_ID]),
        Employees[Emp_ID]
    )
)

 

 

But, this DAX only works for Employees Count.

 

I want the following two things, 

 

  1.  I want the other visuals to also interact when a value is selected from Skills Slicer. That is, Projects count, Practices count as well as the Table showing detail of the employees should also get filtered when a skill is selected, based upon the selection of user. 
  2. I want the slicers of Practice and Project to interact with the Skills Slicer as well. That is, if a skill is selected, then it should only show those practices and projects in the slicer which correspond to the employees of that Practice/Project.

How can I achieve these two things without possibly changing the direction of Relation to Bidrectional?

 

Can anyone help out in this? Thank you.

 

If anyone would like to download the Power BI File, you may do so from here: https://drive.google.com/file/d/1fyImZVMsTLGllUztRgXwjX3Zs6IOLHV9/view?usp=sharing

 

 

If anyone is interested in looking at the dataset, 

 

This is the Fact Table Data, 

Emp_IDProjectPracticeAllocation
21911115Java104.35%
21911633Java100.00%
29000525Mobile15.65%
21853525AI82.61%
29000525Mobile88.70%
29501DummyAI100.54%
285452021Mobile104.35%
23374T187Web100.00%
29508T041Web100.00%
19105T096Web100.00%

 

This is the Employee Table Data, 

 

Emp_IDResource_Name
21911Novak Jah
29000Ali Ahmed Mughal
21853John Smith
29501John Morrison
28545Ali Moeen
23374John Ali
29508Henry 
19105Joe Thomas
  

 

This is the Employee Skills Table Data, 

 

Emp_IDSkill
21911Python
21911Java
21911Machine Learning
29000Java
21853Java
21853NLTK
29000Java
29000Python
29501Python
29501NLP
28545Tableau
28545Power BI
23374NLTK
29508Python
19105Javascript
19105Python
19105Mobile Development
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Create dimension tables for skills, practices and projects and link them to the fact tables

johnt75_0-1682507333819.png

 

Add a filter to the table visual so that [Total employees] is not blank and create a couple of measures like

Show practice in slicer = 
VAR VisibleEmployees = TREATAS( VALUES( 'Employee Skills'[Emp_ID]), 'Employees'[Emp_ID])
VAR VisiblePractices = CALCULATETABLE(
	VALUES( 'FactTable'[Practice]),
	VisibleEmployees
)
RETURN IF( SELECTEDVALUE( 'Practices'[Practice]) IN VisiblePractices, 1)

Show project in slicer = 
VAR VisibleEmployees = TREATAS( VALUES( 'Employee Skills'[Emp_ID]), 'Employees'[Emp_ID])
VAR VisibleProjects = CALCULATETABLE(
	VALUES( 'FactTable'[Project]),
	VisibleEmployees
)
RETURN IF( SELECTEDVALUE( 'Projects'[Project]) IN VisibleProjects, 1)

and add these as visual filters to the slicers, to only show when the value is 1.

 

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

Create dimension tables for skills, practices and projects and link them to the fact tables

johnt75_0-1682507333819.png

 

Add a filter to the table visual so that [Total employees] is not blank and create a couple of measures like

Show practice in slicer = 
VAR VisibleEmployees = TREATAS( VALUES( 'Employee Skills'[Emp_ID]), 'Employees'[Emp_ID])
VAR VisiblePractices = CALCULATETABLE(
	VALUES( 'FactTable'[Practice]),
	VisibleEmployees
)
RETURN IF( SELECTEDVALUE( 'Practices'[Practice]) IN VisiblePractices, 1)

Show project in slicer = 
VAR VisibleEmployees = TREATAS( VALUES( 'Employee Skills'[Emp_ID]), 'Employees'[Emp_ID])
VAR VisibleProjects = CALCULATETABLE(
	VALUES( 'FactTable'[Project]),
	VisibleEmployees
)
RETURN IF( SELECTEDVALUE( 'Projects'[Project]) IN VisibleProjects, 1)

and add these as visual filters to the slicers, to only show when the value is 1.

 

That's an amazing solution! Thank you. 

Just one thing, this doesn't seem to work with the Total Projects and the Total Practices KPI Cards 😕 

 

I am not able to put the "Total Employees" measure in the visual level filter for them. Is there any possible way to get those numbers to work with the Skills as well? 

You could use the same principle as in the measures for the visual filters, 

Total Projects = 
VAR VisibleEmployees = TREATAS( VALUES( 'Employee Skills'[Emp_ID]), 'Employees'[Emp_ID])
VAR VisibleProjects = CALCULATETABLE(
	VALUES( 'FactTable'[Project]),
	VisibleEmployees
)
RETURN COUNTROWS( VisibleProjects )

Total Practices = 
VAR VisibleEmployees = TREATAS( VALUES( 'Employee Skills'[Emp_ID]), 'Employees'[Emp_ID])
VAR VisiblePractices = CALCULATETABLE(
	VALUES( 'FactTable'[Practice]),
	VisibleEmployees
)
RETURN COUNTROWS( VisiblePractices )

@johnt75  That worked! Thank you so much! 

I just need to know one more thing. 

All these DAX Measures look quite difficult to write. Is it really the best practice to write such long/complicated DAX Measures rather than turning on the Bidrectional filtering between Employee and Employee Skills Table? (which I believe should also fix the issue)

 

Also, not to mention, that in a larger dataset, all these DAX Measures will also be quite slower to execute. Can you please provide any guidance regarding this? I would be very thankful to you! 

Bi-directional filtering is probably an option in this case but I try to avoid it wherever possible as it introduces another level of complexity that isn't really necessary. I find moving filters around by using TREATAS easier to understand, and it only happens when I want it to, not all the time. The same effect could achieved by manually turning the relationship bi-directional, using CROSSFILTER, only in those measures where you wanted it.

In terms of performance, I think it would depend on a number of factors. Each model would be different, and you would need to do tests using both methods to see which performed best.

Yes, but I can't really use CrossFilter for a Slicer from what I know, which is why I had a problem using that in this case.

 

Thank you so much for the help and the clarity however! I need to understand TREATAS better and get used to it better. I greatly appreciate you taking out time and helping out, thank you! 

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.

Top Solution Authors