March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have three Tables in my Data Model,
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,
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 |
Solved! Go to Solution.
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.
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
22 | |
20 | |
18 |