Data modeling challenge with multiple fact tables and many-to-many relationship

[mattmca1]

I am running into issues when trying to figure out the correct way to model the following situation:

• A simplified version of my data has Jobs, Product Types, and Features
  • Each Job has a single Product Type
  • Each Job can have multiple or no Features
• I am trying to use Features as a filter
  • If I filter by a specific Feature, I only want to see Jobs that have that Feature
  • If I omit a Feature in the filter, I don't want to see any of the Jobs that have that feature (this seeems like it could be another challenge on top of the issue I'm already encountering)

Here is my current model and a basic version of the visualization which I'm trying to apply the filter to. This setup is NOT working currently and filtering by FeatureName doesn't change any of the data in the visualization:

Here is the sample data in the tables.

Job Facts

JobNum	Quantity
1	500
2	3000
3	250
4	15000
5	8000

Job Dimensions

JobNum	ProductType
1	A
2	A
3	B
4	C
5	B

Job Features

JobNum	FeatureCode
1	X
1	Y
3	X
4	Q
4	Y
4	Z
5	Z

Features

FeatureCode	FeatureName
Q	Feature Q
X	Feature X
Y	Feature Y
Z	Feature Z

Has anybody run into a similar scenario, and how did you solve it?

[mattmca1]

I ended up figuring out how to fix the 2nd issue I had (where I wanted a filter that would exclude specific features). Recording it here for posterity. I create a measure that will return a 1 for any job that is in the set of excluded features that the user selected to filter by, and a 0 otherwise. I then filter the visual to only show jobs where this measure is 0 (so only jobs that aren't excluded by the feature filter).

HasExcludedFeatures = 

VAR FeaturesSelected = COUNTROWS(Features)
VAR AllFeatures = COUNTROWS(ALL(Features))

RETURN
IF (
    FeaturesSelected = AllFeatures,     -- If no filter is set, show all jobs
    0,
    IF (
        VALUES('Job Dimensions'[JobNum]) IN
            EXCEPT (
                VALUES('Job Dimensions'[JobNum]),
                DISTINCT('Job Features'[JobNum])
            ),
        0,
        1
    )
)

[mattmca1]

Thank you @amitchandak. That solved most of the issue.

One part I'm still having an issue with and I can't wrap my head around how to approach it is to also use the Features filter to omit certain jobs. For example, let's say I don't want to see any Jobs that have the X feature. If I just uncheck the X Feature in the filters list, it will still show Job 1 because that job also has other features besides X. Any idea how to set up the filter to act this way?

[amitchandak · Accepted solution]

Try like

Fact <-( Many to 1) - Job <- (Many to Many ) <- Job Features <- Many to one <-  Feature

Arrow shows direction filter

[mattmca1]

I think I've got it set up the way you explain:

The behavior I'm looking for is:

• If I filter by Feature X, it will only show me info for Job 1 and 3 (this now works correctly with your table setup)
• If I exclude Feature X from the filter, it will exclude any Jobs that have feature X, so it would only show me info for Job 2, 4, and 5. However, it's currently showing me Job 1 (since that Job has additional Features besides X) and not Job 2 (because it doesn't have any Features at all). I'm OK with this being a separate filter because I'm not sure that I could get the functionality of both of these points with a single filter.

[amitchandak]

@mattmca1 , Join Job features directly with Jobs Fact, Change join to many to many and direction of filter from Job features to  Jobs Fact

Also change join between Job dimesion and Job Fact 1- Many  (prefer single directional)