topic Re: How to use a slicer from Table on the Many-to-one side of Dimension Table in DAX Commands and Tips

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

Anonymous — Wed, 26 Apr 2023 09:34:37 GMT

I have three Tables in my Data Model,

Fact Table: It contains all the records, along with two columns called "Project" and "Practice"
Employee Table: It contains records of all the employees.
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,

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,

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)

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,

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.
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_ID	Project	Practice	Allocation
21911	115	Java	104.35%
21911	633	Java	100.00%
29000	525	Mobile	15.65%
21853	525	AI	82.61%
29000	525	Mobile	88.70%
29501	Dummy	AI	100.54%
28545	2021	Mobile	104.35%
23374	T187	Web	100.00%
29508	T041	Web	100.00%
19105	T096	Web	100.00%

This is the Employee Table Data,

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

This is the Employee Skills Table Data,

Emp_ID	Skill
21911	Python
21911	Java
21911	Machine Learning
29000	Java
21853	Java
21853	NLTK
29000	Java
29000	Python
29501	Python
29501	NLP
28545	Tableau
28545	Power BI
23374	NLTK
29508	Python
19105	Javascript
19105	Python
19105	Mobile Development

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

johnt75 — Wed, 26 Apr 2023 11:11:05 GMT

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

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.

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

Anonymous — Wed, 26 Apr 2023 16:15:21 GMT

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?

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

johnt75 — Thu, 27 Apr 2023 08:20:41 GMT

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 )

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

Anonymous — Thu, 27 Apr 2023 08:31:48 GMT

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!

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

johnt75 — Thu, 27 Apr 2023 08:44:16 GMT

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.

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

Anonymous — Thu, 27 Apr 2023 09:21:02 GMT

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!