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

[HassanAshas]

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, 

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

and in my visuals, I want to show a table containing records of all employees (along with their skills. There will be one column for Skills where all skill values will be concatenated. This wouldn't be a big problem I believe but the problem I am facing is the Data Model) 

Similarly, I have another visual of Bar Chart that shows Total Employee Count by each skill. This is a problem because I am not able to obtain correct results for these visuals. Especially considering that my visual should work if any of the Project or Practice is filtered. 

This is the report I currently have, 

This is the measure I am using for Employee Count, 

Total Employees = DISTINCTCOUNT(FactTable[Emp_ID])

Now, I can use the Emp_ID from the Employee_Skills Table but it wouldn't work because the two filters I have will not propagate to that level. 

Can anyone guide me what can be the best possible way to tackle this problem? 

I know I can tackle this by merging Employee Table with Employee Skills table, but it would create Many to Many relation with fact table. 

I know I can also tackle this by enabling Bidirectional filtering between Employee Skills and Employee table, but I have always read that Bidirectional filtering should be avoided at any cost.

I tried to solve this problem by enabling Bidirectional filtering only for my Measure using Crossfilter but I guess I couldn't achieve it properly. 

If anyone would like to download the Power BI file, you may do so from here: https://drive.google.com/file/d/1Ud346XhRI15gLsKsCj7ghhuqen5eOuGm/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

[ppm1 · Accepted solution]

For the distinct count from your skills table, you can just use a simple DISTINCTCOUNT() measure. But if you have a slicer from a column in your skills table and you need to filter your Employee or fact tables, just reverse the columns in the TREATAS.

Employee Count = 
CALCULATE(
    [Fact or Employee Measure],
    TREATAS(
        DISTINCT('Employee Skills'[Emp_ID]),
Employees[Emp_ID]
    )
)

Pat

[DimaMD · Accepted solution]

@HassanAshas Hi You need to use this measure

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

[HassanAshas]

Hi @DimaMD  Thank you so much for the response. This worked perfectly! 

Would it be also possible for you to please tell me why did it work and why did the other DAX command did not work? 😕 

I was using this DAX Command, 

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

But it didn't give me correct result however just like you changed the order of the measures within this DAX, it started to work. I am failing to understand what's the difference and how did the filter propagated in this DAX of yours so that it started to work. 
I would be very grateful to you! 

[DimaMD]

Hi @HassanAshas  Your measure is also correct, but you are using DISTINCT (creates a virtual table) in the TREATAS relation, it is correct to use VALUES.
Check out some great articles on functions and relationships between tables.
https://dax.guide/distinct/
https://dax.guide/values/
https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

If you use your measure but with the VALUES function, your expected result will also be true.

[ppm1]

You are right to keep your model as is (star schema). To pass the filter from the skills table to the other tables you can use CROSSFILTER or TREATAS inside CALCULATE.

Pat

[HassanAshas]

Hi @ppm1 
Thank you for the suggestion. Is it possible for you to kindly provide a sample DAX for a scenario similar to this? 
I tried to use the following, 

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

It did show me the correct result but my slicers of Project and Practice don't work with this. That is, in my bar chart, I did get the correct result for each skill, but when I filtered Project or Practice, the Bar Chart didn't get filtered and still showed the same picture as before (when no filtering was applied) 

[ppm1 · Accepted solution]

For the distinct count from your skills table, you can just use a simple DISTINCTCOUNT() measure. But if you have a slicer from a column in your skills table and you need to filter your Employee or fact tables, just reverse the columns in the TREATAS.

Employee Count = 
CALCULATE(
    [Fact or Employee Measure],
    TREATAS(
        DISTINCT('Employee Skills'[Emp_ID]),
Employees[Emp_ID]
    )
)

Pat

[HassanAshas]

Hi @ppm1 

Thank you so much! This one worked perfectly! 
But I didn't understand one thing, how come just by reversing the columns in the TREATAS made it work but it didn't work with the same structure 😕 

I understand it has something to do with how filter is propagating but I can't seem to get my head around it. 
Can you please also just give me a short description of what's happening in the background? I would be very thankful to you! 

[ppm1]

Glad it worked. In TREATAS, the first term is the table of filter values and the second is the column to apply them to.

TREATAS - DAX Guide

Pat