• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Working with TWO fact tables - filter propagation|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Working with TWO fact tables - filter propagation|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotWorking with TWO fact tables - filt…
sp_PrintTopic sp_TopicIcon
Working with TWO fact tables - filter propagation
Avatar
Blanka Blair
Member
Members
Level 0
Forum Posts: 53
Member Since:
October 17, 2015
sp_UserOfflineSmall Offline
1
October 29, 2019 - 6:11 am
sp_Permalink sp_Print

Dear All,

My Power Pivot knowledge is basic at best, but I am attempting to create a dashboard using Power Pivot. The amount and complexity of our data was just too much for Power Query alone to handle.

I am attaching a file with a simplified sample of the data.

I have two fact tables - Policies and Claims. Some of the columns are in common - for example State. I created a "lookup" table for State and when I insert a pivot table based on this common column, all values work out fine - see Example # 1 in my file.

Another common field between the two tables is Policy number. In Policies table it is always a unique value, in Claims table it can repeat.

I created a "PolicyID" table as a "lookup" table to join Policies fact table to Claims fact table.

Here is where my troubles start - if I try to create a pivot table with row values from Policies table (for example Construction), then amounts from Claims table are incorrect - Example # 2 in the file. If I create a pivot with row values from Claims table (for example Claim Status), then all the values from Policies are incorrect - Example # 3 in the file.

I am pretty sure that special measures have to be written for this, but I have no idea how. I remember that the filters flow down from look up tables to fact tables, not uphill. I tried variations of CALCULATE with FILTER, VALUES, etc. but clearly I don't know what I'm doing. I don't even know if it's a simple solution, or a complicated one.

Can anyone help, please?

Thank you,

Blanka

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
October 29, 2019 - 9:27 am
sp_Permalink sp_Print

Hi Blanka,

If you want to look at claims by 'Construction' then you need to assign that field to the Policy number in the PolicyID table. Then you'd use the 'Construction' field from the dimension table; PolicyID.

You can easily change the Query for PolicyID to not remove the 'Construction' column. 

Mynda

Avatar
Blanka Blair
Member
Members
Level 0
Forum Posts: 53
Member Since:
October 17, 2015
sp_UserOfflineSmall Offline
3
November 2, 2019 - 7:08 am
sp_Permalink sp_Print

Hi Mynda,

Thank you for your reply.

I have many more fields in my data that I need to use for analysis. Construction was just an example. And this would not solve my problem if I wanted to look at data by fields from the Claims report, would it?

For some reason I thought that the answer would be in the measures.

I'll have to keep playing with the data to figure out how best solve it.

Thank you,

Blanka

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 2, 2019 - 1:18 pm
sp_Permalink sp_Print

Measures can only work if the data structure is correct and the relationship exists. If you take the Construction scenario and understand how the model needs to be set up for that to work, hopefully you can apply it to your other fields.

See example attached, I've fixed the structure and added two PivotTables to the Policy Sheet. Notice where the fields come from in the Rows area; they are the dimension table that relates the two fact tables (tblClaimes and Policies) together.

I think you need to review the Power Pivot course session on modelling 6.07 and 6.08.

Mynda

Avatar
Blanka Blair
Member
Members
Level 0
Forum Posts: 53
Member Since:
October 17, 2015
sp_UserOfflineSmall Offline
5
November 12, 2019 - 8:15 am
sp_Permalink sp_Print

Thank you so much, Mynda.

I'll work on my data following your example.

Blanka

Avatar
Blanka Blair
Member
Members
Level 0
Forum Posts: 53
Member Since:
October 17, 2015
sp_UserOfflineSmall Offline
6
November 15, 2019 - 2:49 am
sp_Permalink sp_Print

Mynda,

This didn't work for me the way as intended, because some policies can have multiple claims and some of those claims can be open while others can be closed, each claim can have a different Claim Type. In the sample data that I submitted there were three policies with multiple claims and so happened that the claims for each of these policies had the same status.

The PQ that you created for my dimension table removes duplicate policy numbers after merging with Claims Data. This will not account for the fact that one policy can have multiple Claim Status values. Or that one policy can have multiple Claim Type values. For example, Policy A00788 has one Claim Type = Fire, and one Claim Type = Property Damage.

If I don't remove the duplicate policy numbers from my dimension table then I am getting an error message that duplicate values are not allowed for columns on the one side of a many-to-one relationship.

So, I still don't know how to relate Policy data to Claims data. Is it even possible for my data model?

Thank you,

Blanka

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
November 15, 2019 - 2:05 pm
sp_Permalink sp_Print

Hi Blanka,

I don't see why you can't use the Claim Type field from the Claims table. Please provide an example of the error and what the correct result should be because checking policy A00788 I can see the correct amounts for the claim types, construction, premium, claim incurred and claim count when I add the 'ClaimType' column to the rows area from the 'tblClaims'.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ngoc Tinh, Dieneba NDIAYE, Lavina Dhanwani, Natasha Smith, Monique Roussouw
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27252

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.