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?


 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mikeincairns2

New Member
Joined
Dec 15, 2016
Messages
11
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
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
272
Office Version
365
Platform
Windows
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])
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
272
Office Version
365
Platform
Windows
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.
 

mikeincairns2

New Member
Joined
Dec 15, 2016
Messages
11
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,102,682
Messages
5,488,241
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top