Using DAX formulas in Pivot Tables

When calculating data in an Excel pivot table, you're not limited to the listed options in the value field settings under the summarize value field by selection. There are lists of calculations including the sum, average, max, and minimum values for data in the pivot table. You can also input DAX, or Digital Analysis Expression, formulas in the table, which will return a table as a result. A pivot table doesn't include median values in the list of preset values, but you can enter a DAX formula which will add it to the table.

Follow these steps explained by Bill Jelen here.

1. When selecting the data range for the pivot table select the option to add the data to the Data Model.

2. With the data set up this way, you'll see that in the PivotTable Fields box, you have the option to 'Add Measure' when you right click on the Range.

3. Give the new measure a name, and then enter MEDIAN as the formula selecting the field you want to analyze.

4. Set the appropriate number format, and use the Check DAX Formula tool to confirm that the formula will function.

5. The MEDIAN DAX formula is now listed as a new field for the Pivot table can be added to the displayed values.

Note that in this data set, the zero values are not an error. This is just showing that in each of the selected years most players had no stolen bases - except for 1908, when a majority of players had at least one stolen base.

OnCue's designation wizard

It's no secret that attorneys will list deposition designation page and line ranges in a variety of formats. The same attorney may use a page / line range form inconsistently, and of course, even the best lawyers will inevitably enter page and line numbers incorrectly. Like nearly all litigation support professionals, I have spent many hours parsing through lists of designations to put them in the format required by TextMap, Trial Director, and other applications.

Luckily, OnCue's trial presentation software includes a 'Designation Wizard' which can import page and line ranges that have been listed in a variety of forms. Not just loads files in the format used by Lexis TextMap (as discussed in the Tip of the Night for May 5, 2024), or the format used by Sanction, but in almost any random form an attorney might come up with.

In this example, we can see how page line numbers entered with different delimiters (both using common delimiter characters) and common terms used in English), get interpreted as designation ranges by OnCue which then automatically generates designation snippets. If it can't tell that range is intended, it will highlight the unknown range in red:

Even when ranges are entered with extra spaces between page and line numbers, or with different delimiters for a single range, OnCue can still get them correct:

You copy ranges in, or enter them one by one, and the Designation Wizard creates the new video clips almost in realtime.

The Wizard will help you catch mistakes you might have otherwise missed, and get video clips finalized for the next morning faster, so you can get to bed sooner - the key challenge every trial tech faces.

How to add links to PDF files in a court filing submitted on a flash drive

Courts frequently request that a copy of a court filing be submitted on a flash which contains hyperlinks to exhibits which are cited in the filing. If you don't want to add the url for where an exhibit was filed on PACER or on a state court docket, and you don't want to create one long, 100 MB plus PDF with internal links to each exhibit, it is possible to add links to PDFs of the exhibits which are on the same flash drive as the PDF of the main filing.

Obviously, anytime a flash drive is inserted in a different PC it can be assigned a different drive letter. Acrobat provides a way to add 'relative paths' which omit a full file path including a drive letter, and will simply point to the same location the open PDF is located in.

Follow these steps:

1. Put both the main document and the documents you are linking to in the same folder on the flash drive.

1. Click ‘Edit PDF’ from the toolbar:

 3. Select the option for ‘Add/Edit web or Document Link’

1. Choose the option for ‘Open a web page’, not ‘Open a file’

1. Just enter the filename of the document you are linking to – not the full filepath.  Remember each document you are linking to should be in the same folder as the court filing or whichever file has the links.

Now no matter where the files in the folder are copied to, Adobe Acrobat should open a relative path – it will open the linked files assuming that they are in the same folder as the PDF the links have been added to.

I tested this method by copying the files to more than one location on a network, and also with the data copied to a flash drive used with two different laptops, and then with the data copied off the drive and onto the hard drive of each laptop.  The links worked each time.

The only problem I encountered was when I tried to open the links using an open source PDF editor (for which I did not have a full license) instead of Acrobat.  However, the links did work when I simply opened the PDFs in Edge or Chrome, so if a recipient does not have Acrobat, they will still certainly be able to open a copy of the PDF with working hyperlinks.