What is the best approach
Results 1 to 5 of 5

Thread: What is the best approach
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default What is the best approach

    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?



  2. #2
    New Member
    Join Date
    Dec 2016
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What is the best approach

    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

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What is the best approach

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

  4. #4
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What is the best approach

    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.

  5. #5
    New Member
    Join Date
    Dec 2016
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What is the best approach

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •