• 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

Filtering not working from data model|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Filtering not working from data model|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 PivotFiltering not working from data mod…
sp_PrintTopic sp_TopicIcon
Filtering not working from data model
Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
1
September 1, 2020 - 7:35 pm
sp_Permalink sp_Print

Hello folks - anyone into Power Query with Power Pivot?

I'm asking some of you to dive into this little prepared example, as I'm really not able to make the slicers connect properly to work.

All attached output-with-filters-slicers.pngImage Enlarger

data are for testing purpose.

Input sheets:
- UBW (economics data)
- Contracts and Rentals (both are mixed in Query and Power Pivot into a common table Agreements)

Output sheet:
- Analytics

Problems:
- In output sheet I miss a button red marked, since most important is to filter those transactions there are no agreements at all (neither contracts or rentals). If amount > 100' sum each supplyer, those can be not allowed, and must be followed up.
- Slicers "From period" and "To period" does not work, neither does the slicer for Type. I've tried to make an uniq extract of Types, but then miss as said, the important button red marked.

Thanks in advance, if anyone is able to make these slicers work properly for me (ps: check amounts sum).

Trond

sp_PlupAttachments Attachments
  • sp_PlupImage output-with-filters-slicers.png (189 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 1, 2020 - 9:11 pm
sp_Permalink sp_Print

Hi Trond,

You're asking Excel to show you the UBW Amounts classified by Type, but the UBW table doesn't have a Type column, and the related uniqVAT table also doesn't have Type. The only table with Type is the Agreements table. If the Type is related to the VAT code or SupplNr then you should have the Type column in the uniqVAT table as opposed to a separate Type table.

Similarly, the From and To Dates have no relationship to the UBW data because the UBW table doesn't have those same fields, therefore they cannot filter it.

You can't display the suppliers with no contracts because the uniqVAT table doesn't contain the VAT for 'other company'. 

I must admit, it's not clear how this data should be related. I don't know how VAT is related to Suppliers or Customers. It appears to be related to Suppliers but one supplier can have multiple VAT codes e.g. supplier 113, which would make things difficult.

I hope that helps you identify what you need to change.

Mynda

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
3
September 1, 2020 - 10:27 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mynda,

You write "Similarly, the From and To Dates have no relationship to the UBW data because the UBW table doesn't have those same fields, therefore they cannot filter it."

Q1: If I include period (example 202004, 202006 etc for YearMonth) in UBW table, could that make any difference regarding filtering?

Q2: Must the filtering part of a table in Power Pivot, always be the one with uniqe values?

Trond

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 2, 2020 - 9:10 am
sp_Permalink sp_Print

Hi Trond,

To answer Q1, yes, but you should make these proper dates and then create a date/calendar dimension table so that you can insert a Slicer that filters both the UBW and Agreements tables based on those dates. You will need to have one relationship as inactive and then write a measure that activates it.

To answer Q2, kind of. Tyipcally the row and column labels and Slicer fields come from the fields in the dimension tables and the value fields come from the fact tables.

It's all getting quite complicated and I suspect your Power Pivot skills aren't at the level they need to be to create this model. If you'd like to get them up to speed quickly, I recommend taking my Power Pivot course.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lynnette Altomari, Jessica Stewart, Roy Lutke, Jeff Krueger
Guest(s) 12
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:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
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.