[bigblueParticipant Started: 0Replies: 2Total: 2]

Nice!  I’ve gone one step further by time-slicing on Orders[OrderDate].  The statement below returns the expected results, although I’m not certain that it’s the most optimal (but I’m learning, so I can live with ugly and inefficient for the moment; perfection comes later).

What I had to do was this:

• Create a linked table named LaneRankDates that contains a [Start Date] column and an [End Date] column.
• Create a timeline
• Create a command button that, when clicked, executes some VBA that will:
  • Query the timeline for its start and end dates
  • Update the LaneRankDates linked table
  • Force a refresh of the connection that feeds my big data table

I’ve attached a copy of the workbook with some sample data, if anyone is interested.

Also, Tom, thank you so much for your help in the previous posts!  You really helped me get over the hump.  I’m definitely eager to add this BI skill set to my repertoire, and the Power Pivot Pro forums have proven to be a gold mine.  Keep up the teaching!

Thanks, Garrett

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            Orders,
            Orders[OrderDate] >= MIN ( LaneRankDates[Start Date] )
                && Orders[OrderDate] <= MAX ( LaneRankDates[End Date] )
        )
        , BillTo[BillToName]
        , Shipper[ShipperName]
        , Consignee[ConsigneeName]
        , Commodity[CommodityName]
    ),
    "Order Count", CALCULATE (
        COUNTROWS ( Orders )
        , FILTER (
            Calendar,
            Calendar[Date] >= MIN ( LaneRankDates[Start Date] )
                && Calendar[Date] <= MAX ( LaneRankDates[End Date] )
        )
    )
)
ORDER BY [Order Count] DESC

Private Sub cmdRefreshLaneRanks_Click()
    Dim StartDate As Date
    Dim EndDate As Date

    If Not ActiveWorkbook.SlicerCaches("Timeline_Date3").FilterCleared Then
        StartDate = ActiveWorkbook.SlicerCaches("Timeline_Date3").TimelineState.StartDate
        EndDate = ActiveWorkbook.SlicerCaches("Timeline_Date3").TimelineState.EndDate

        Range("LaneRankDates[Start Date]").Value = StartDate
        Range("LaneRankDates[End Date]").Value = EndDate

        ThisWorkbook.Connections("LinkedTable_LaneRankDates").Refresh
    End If
End Sub

 

Attachments:

You must be logged in to view attached files.

[bigblueParticipant Started: 0Replies: 2Total: 2]

Well, that was easy!  I had a sneaking suspicion that I was headed down the wrong path…

But… the next question concerns the sort order.  After creating the flattened pivot table, I try to sort descending on the order count column.  I would expect to see 80, 64, 51, 39, 3, 1, and 1, but I get this instead, with the little sorted-down-arrow next to the Commodity column header.  I’ve attempted to create a new measure, Order Count by Lane, but I can’t seem to formulate anything that results in a different presentation.
BillTo Shipper Consignee Commodity Order Count
B1       S1           C1                CMD1            1
B1       S1           C2               CMD1             51
B1       S1           C2               CMD2            1
B1       S2           C1               CMD3            39
B1       S3           C3              CMD3            3
B1       S3           C4              CMD3            60
B2      S4            C1              CMD2            84

Thanks again, Garrett

[Author]

Posts