What is the best approach

mikeincairns2

New Member
Joined
Dec 15, 2016
Messages
11
Hoping someone can give me a start. Difficult to explain so have attached an image.

Have a list of ITEMS. Also have a table of TESTED ITEMS. Need to create a PT to report number of items tested vs items not tested. Want to have a date slicer to select the year.

How to start please?


y3mgJcg92zaJb9Q7FSMU0vngBTmXVI8AFA8PVU16e9ul-g0e16gSOu0LlwN0IDxNK9LoXp9EQ2JANynxvfvnWnUW0yiFVKSBYPn4hUcKuIMszCLPUmMjBz3zwjs-ZeAhJeOPvMGtzNcPigghJWXom4EVvse7AcYe2_0i6Wf6N-ATMs
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sorry - should have mentioned the data is in Access and I want to use Excel 2016 Data Model (but I think this does not determine the overall approach). Ta. Mike
 
Upvote 0
These are very broad strokes, but others can add more detail. Since you're only showing a couple of columns for each table we won't worry about changing or removing columns in the model.

1) Use PowerQuery to import the two tables: item master and test history. This is a long post in itself, but in quick terms you can go to the Get & Transform section of the Data ribbon, choose From Database, and follow the instructions.
2) in the data model, relate the item master to the test history based on the item ID. Assumes item ID is the same data type in both tables and that it's unique in the item master. Again a longer post, but find the Diagram View and drag the item ID field from one table on top of the item ID field of the other table.
3) Go into the data model (Alt+B,M). click on the Item Master tab and scroll all the way to the right to the last column called "Add Column".
4) Click on a cell in the column. Create the calculated column in the item master file, call it "Tested". If there are any rows against the item ID in the test history file return TRUE (or Y) else FALSE (or N) depending on what value you want in the slicer.
(Following DAX is just a guideline, you may need to tweak it depending on your circumstances)
IF(COUNTROWS(RELATEDTABLE(Test History))>0,"Y","N")
5) Inside the data model window on the Home tab click the PivotTable icon. Create the pivot table. The new "Tested" column is your slicer source. Drag the Item Master[Item ID] into a row of your pivot table and the Test History[Year] as well. You may need to create a dummy measure to put in the values section in order for the filters to work properly. You can do a count of the number of test years
Dummy Measure:= COUNTROWS(Test History[Year])
 
Upvote 0
Re-reading your post it seems you may have wanted to show all items in a selected year, both tested and untested. If you just want to show tested items you can build your pivot table only from the item history file and use the year slicer from the year field. If you want to show both tested and untested items in a given year, I can't give you a simple solution. I only have Excel 2013 so I don't have the bi-directional filtering of 2016 and my solution would probably create unnecessary complexity. Perhaps someone else can chime in with a better recommendation.
 
Upvote 0
Thanks so much for your quick reply macfuller.

I had already done steps 1 and 2 in your post. I have previously tried a calculated column but that as you say in your second post it was not successful.

I have a method that works but I think it is far from elegant. It uses two Pivot Tables with the Distinct Count function (not sure if that is in 2013), and then Getpivotdata formulas to extract what I need. Messy but works.

Got to be a more elegant solution using DAX.

y3miaxU1djnluan_GsJK6oRieYQNSKCpkodIfoCGV-fRb70F6lJ1CURejzMVlnJfbivaKj59A-cGC2gs3odxEvhZHqSdEITNh1ryaXrK5qD4GaE2iVA1qv_E9mPJKCFSJGXehUmTRFO9S25AyIApx7zDpkpckzCKLqmvvSXBrlP8jo
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top