Slicer: unexpected undesired behavior - slices data where no relationship exist

[DAXRichArd]

Hello,

Thank you in advance for your guidance and help.

• I am conducting comparison and time series analysis on commerical aviation data.
  • Data is monthly data.
• I have 4 fact tables
  • Only two dimensions are common in all fact tables.
    • Airport
    • Date

Setup

I created a Star Schema where fact tables share dimension tables.

• All relationships are
  • Cardinality
    • Many to one.
      • Fact table is the many side.
      • Dimension table is the one side.
  • Cross filter direction.
    • Single.
• Ten dimension tables, 9 created in Query. 
  • Data typed into blank table.
  • Tenth table is excel file pulled into Query.
  • D- begins naming convention for dimentions.
• Four empty tables used to organize measures.
  • _Cargo
  • _FAA Ops
  • _Landings
  • _Pax
• Calendar
  • _Calendar (date table)
  • _Calendar tools (time function measures)
• Four fact tables.
  • F-begins naming convention for fact tables.

Output

My output is into a matrix table.

• I used matrix table to test and validate results before creating visualizations.

Problem

I have 2 slicers that are providing unexpected and undesired results.

• region:
  • The reigion dimension exist in only these 2 fact tables.
    • F-Cargo.
    • F-Pax.
  • The region is slicing results in 
    • _FAA Ops measures
    • _Landings measures
      • Neither of these two have these dimensions in their fact tables.
      • No relationship between D-Region and the aforementioned fact tables exist.
• Airport:
  • The Airport dimension exist in all fact tables.
    • _FAA Ops measures
      • Measures using time intelligence and / or to return a result for a previous time period
        • Results in partial output or missing output.
          • Some years are missing a few months, others missing the entire year.

Attempts at Solving Problem

• Reviewed all measures to see if I was incorrectly referencing a column.
• Deleted all relationships.
  • Recreated all relationships testing as I go.
    • This is how I discovered the F-Cargo   D-Region relationship was a problem, but not the only problem.
    • When I delete this relationship, the region slicer no longer affects _Landings and _FAA Ops (even though no relationship exist).

Desired Outcome

Have slicers slice data and have output in matrix table be correct (no blanks where there should not be blanks, aggregations calculated correctly).

History

My original problem (now solved):

• SEE https://community.powerbi.com/t5/Desktop/Time-series-comparative-analysis-sum-incorrect-previous-yea...

FILE

https://myaccount.dropsend.com/file/03d8a92fbd88e230

3.2MB

Screenshots

[lbendlin]

- I have rearranged your data model slightly to make it a bit easier to understand

So far it looks really clean - kudos.

- Next step will be to check which fields feed your slicers. Ideally that should be the columns from the dimension tables. That seems to check out as well.

- Can you describe on a concrete example what undesired behavior is and what you would expect instead?  You have lots of measures - are you confident they all do what they are supposed to do?

- You used DAX to create a calendar. That is not something you normally want to do (but nobody can stop you if you insist).

- Thank you for providing the sample data.  It's too much.  Can you limit the sample data to cover the issue, but not more?

[DAXRichArd]

Hi Ibendlin,

Big thank you for your reply.

Measures

The way I set up the measures are:

• _Cargo (table housing cargo measures) corresponds to the F - Cargo fact table
• Et cetera.

Quantity of measure. I am reasonably confident they do what I want them to do.

• I use the "Training Set Test Set Page" to test and validate results.
• This is how I discovered the un-desired / un-expected results.
  • Note: Measures aggregage integers in fact tables.
    • The measures are not aggreagating text values (e.g. count)

Key Columns

• Two key columns are common for all 4 fact tables:
  • a date column
  • Airport (originally called "campus" when the data tables are queried).

Un-desired / Un-expected results.

F - FAA Ops is where the problem is most obvious.

• The F - FAA Ops fact table has the following columns:
  • Airport
    • EFD
    • IAH
    • HOU
  • operation type
    • Air Carrier
    • Air Taxi
    • General Aviation
    • Military
  • operations
    • This is an integer. 
    • (an operation can be a landing or a take-off)
  • report_date
    • Date of report
• Note: when I queried and cleaned up the data, I removed two columns.
• company_nm
  • The value for all rows for the entire time series is "Federal Aviation Administration."
• statistic_name
  • The value for all rows for the entire time series is "FAA Operations."

Region does not exist in fact table F - FAA Ops.

Problem / Observation

Problem

• No relationship is defined between dimension table Region and fact table F - FAA Ops.
  • Relationships with Region are only with
    • F - Cargo and
    • F - Pax.
• I create a matrix table and populate it with measures from one fact table at a time.
  • Then I test the results using one slicer at a time.
    • Even if there is no relationship with the fact table, I still test the slicer.
• When F - FAA Ops measures populates the matric table, slicer REGION filters the results.
  • This should not happen.
    • SPECIAL NOTE
      • It appears to effect specific measures for previous time periods, e.g.
        
        • Measures (4)
          1. FAA Ops 2019              (year 2019 by month)
          2. FAA Ops LM                 (last month ( [previousmonth] )
          3. FAA Ops LY                   (last year ( [sameperiodlastyear] )
          4. FAA Ops YTD 2019       (year to date for year 2019)

Observation

• When I deactivate the relationship between D - Region and F - Cargo.
  • the Region slicer does not filter F - FAA Ops.
• When I reactivate the relationship between D - Region and F - Cargo,
  • the region slicer filters F - FAA Ops.
• Keep in mind F - FAA Ops does not have a Region column nor does it have data similar to region.
  • No relationship exist
  • Region should not slice F -FAA Ops.

Final Observation

The same occurs when EFD is select in the Airport slicer.

• It does not effect the other airports when selected (HOU, IAH).
  • It only affects EFD.
• Here however there is a relationship between dimension Airport and fact table FAA Ops.

Closing Remarks

Since Date and Airport are common with all tables I thought the problem might be coming from there.

• I reviewed all my measures to see if the behavior is coming from the measure.
• I deleted and re-connected all my relationships in the model.

I can not figure out the problem.

Hope you can help.

Either way I am grateful for your time.

DAXRichard

[lbendlin]

I don't think the problem is in your data model.  Sounds more like a perception issue.  (As the saying goes - computers do what we tell them to do, not necessarily what we want them to do)

As I mentioned before you have provided a bit too much detail, and it is hard to focus on the problem that you are trying to solve. Can you reduce your data and data model to the essential bits that cause the perceived issue?

[DAXRichArd]

Thx Ibendlin,

I think I'll start over instead of trying to salvage this model.

I know your time is valuable to you. I sincerely appreciate your time looking into this.

DAX Richard