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