Data Analysis Tool For All

Intake data from your case management system:

• Fields:
  • Demographics about which you are curious, such as Age at Intake, Gender, Race, Ethnicity, With Disabilities, Veteran, Percentage of Poverty, Number in Household, Citizenship Status, Language, Living Arrangement, County, etc.
  • Dates: Date of Earliest, Intake Date, Prescreen Date, Open Date, Close Date, Date of Rejection, and Date of Birth (for use in an age-related formula).
  • Case Information: Legal Problem Code, Problem Code Categories, Close Reason, Rejection Reason, Disposition, Intake Type, etc.
• Filters/Report Structure:
  • Date Filters: Date of Earliest in the last five years, or Intake Date or Prescreen Date in the last five years.
  • Exclude/Filter Out: Test or fake cases and clients and duplicate cases using whichever fields your organizations uses to identify these cases and clients, such as Rejection Reason, Client Name, Case Status, Funding Code, etc.
  • Include: Cases that were closed with service, rejected without service, and remain open.
  • One Row/Record Per Case: Ensure that downloaded data includes just one record (one row) per case. If necessary, text join fields that cause more than one row/record per case.
• Export Format:

If downloading from LegalServer, it is recommended that report results are exported as CSV files (an option that may be set up under Additional Display Format – remember to check Headers in First Row) and saved as Excel files to avoid formatting issues, particularly related to date fields.

1. Download data from your case management system (CMS), use an API link to connect to data in your CMS, or connect to your CMS data directly via an ODBC connection or other method.
2. Open Microsoft Power BI and select Excel as your data source.

3. Connect to the CMS downloaded data file (from step 1), click the check box next to the tab name and click on Load.

4. Save you Power BI file.
5. Create New Columns
   1. The following New Columns create categories that are helpful for analysis.
      1. Click on the name of your data file in the Data pane and either right click to select New Column or select the New Column button from the Table Tools ribbon across the top.
         
      2. Copy these formulas (over “Column = in the formula bar) into new columns (make sure to update the formula references so that they are pointing to the correct fields in your data). To change field references, highlight the field references in these formulas from opening single quote to closing right bracket (example: 'Intakes 2020-2024'[Percentage of Poverty]), enter a single quote and a list of the fields in your table should appear from which you can choose.
      3. Add these measures before creating visuals.

   2. Poverty Ranges:
      Poverty Ranges =
      
      SWITCH(
      
      TRUE(),
      
      AND('Intakes 2020-2024'[Percentage of Poverty] = 0, ISBLANK('Intakes 2020-2024'[Types of Income])), "Unknown",
      
      AND('Intakes 2020-2024'[Percentage of Poverty] = 0, NOT(ISBLANK('Intakes 2020-2024'[Types of Income]))), "a. Below 100% FPL",
      
      'Intakes 2020-2024'[Percentage of Poverty] < 100, "a. Below 100% FPL",
      
      'Intakes 2020-2024'[Percentage of Poverty] < 125, "b. Below 125% FPL",
      
      'Intakes 2020-2024'[Percentage of Poverty] < 200, "c. Below 200% FPL",
      
      "d. 200%+ FPL“
      
      )

      1. Because some databases enter Poverty=0% when Income is blank, this formula ensures that you do not overcount those who actually have $0 income and 0% Poverty Level.
      2. Click on the green check box next to the formula bar to verify the formula is working and to create the new column.

   3. Race Formula:
      Race Formula =
      
      SWITCH(
      
      TRUE(),
      
      'Intakes 2020-2024'[Race] = "Black or African American", "Black", 'Intakes 2020-2024'[Race] = "American Indian or Alaska Native", "AIAN",
      
      OR('Intakes 2020-2024'[Race] = "Other", 'Intakes 2020-2024'[Race] = "Native Hawaiian or other Pacific Islander"), "Other",
      
      OR('Intakes 2020-2024'[Race] = "", 'Intakes 2020-2024'[Race] = "Prefer not to disclose"), "Unknown",
      
      'Intakes 2020-2024'[Race]
      
      )

   4. Served or Not:
      Served or Not =
      
      SWITCH(
      
      TRUE(),
      
      'Intakes 2020-2024'[Rejection Reason] <> "", "Not Served",
      
      ISBLANK('Intakes 2020-2024'[Close Reason]), "Remaining Open",
      
      LEFT('Intakes 2020-2024'[Close Reason], 1) IN {"A", "B", "F", "G", "H", "I", "K", "L"}, "Served",
      
      LEFT('Intakes 2020-2024'[Close Reason], 1) IN {"R", "X"}, "Not Served",
      
      "")

      1. This formula divides cases up into Served, Not Served, & Remaining Open categories.
6. Build Visuals in the Report View screen
   1. There are Filter, Build, and Data panes in the Report View screen.
   2. Visual options appear in the Build pane.
      
   3. You may try different visuals, but for trend analysis, try either the Line, Stacked Column, or 100% Stacked Column charts. This example shows The Line & 100% Stacked Column charts.
   4. Build Line Chart: Click on the Line Chart Icon and it will appear in your Report View.
      1. Adding Year to Chart: In the Data Pane, click on the downward pointing arrow next to your data file name to be able to see all the fields in your file. Click on > next to Date of Earliest to open up the Date Hierarchy. Click again on > next to the Date Hierarchy to see Year, Quarter, Month, Day. Drag Year into the X-axis box in the Build Pane.
         
      2. Adding Number of Cases to Chart: Drag Matter/Case ID into the Y-axis box in the Build Pane. Click on > next to the field and change the Summarization to Count (Distinct).
         
      3. Adding Problem Code Categories to Chart: Drag Problem Code Category into the Legend Box in the Build Pane.
      4. Adding Served or Not to Chart: Drag the newly created column Served or Not into the Small Multiples box in the Build Pane.
         
      5. Change Filters (in the Filter Pane): Click on the downward arrow next to Problem Code Category and change the filter so Blank is not included. Click on the downward arrow next to Served or Not is (All) and change the filter so that only Served & Not Served show.
         
      6. Suggested Formatting Changes (in the Format Pane):
         1. Add Border: Under Size and style, click Visual border to on.
         2. Title: Edit the text of the Title and change the font size, coloring, and positioning.
         3. X-axis: Turn Title off. Change the font of the Values (Years) under the Values subsection.
         4. Y-axis: Turn Title off. Change the font and/or Display Units of the Values (count of cases) under the Values subsection.
         5. Legend: Turn Title off. Change the positioning and font.
         6. Small Multiples: Change the number of rows to 1.
7. Data Format: In the Properties section of the Format Pane, under Data format, change Apply settings to Count of Matter/Case ID, change Format to Whole Number and turn on the Thousand separator. Doing this will show the numbers in easier to read format when viewers hover over any point on the chart to see the Tooltips information.
   
8. Category Order: To have Served appear before Not Served, click on the three-dot ellipsis at the top right corner of the chart to open up More Options. Under Sort small multiples, select Served or Not and Sort descending.
   
9. Build 100% Stacked Column Chart: Click on the 100% Stacked Column Chart Icon and it will appear in your Report View.
   1. Adding Year to Chart: In the Data Pane, click on the downward pointing arrow next to your data file name to be able to see all the fields in your file. Click on > next to Date of Earliest to open up the Date Hierarchy. Click again on > next to the Date Hierarchy to see Year, Quarter, Month, Day. Drag Year into the X-axis box in the Build Pane.
      
   2. Adding Number of Cases to Chart: Drag Matter/Case ID into the Y-axis box in the Build Pane. Click on > next to the field and change the Summarization to Count (Distinct).
      
   3. Adding Served or Not to Chart: Drag the newly created column Served or Not into the Legend box in the Build Pane.
   4. Adding Problem Code Categories to Chart: Drag Problem Code Category into the Small Multiples in the Build Pane.
      
   5. Change Filters (in the Filter Pane):
      1. Top 5 Problem Code Categories Excluding Blanks: Click on the downward arrow next to Problem Code Category and change the filter so Blank is not included. Drag the Problem Code Category field over into another Add data fields here box in the Filter Pane under Filters on this visual. Change the Filter Type to Top 5 and enter Matter/Case ID in the By value box, changing it to Count (Distinct). Click on Apply Filter.
         
      2. Show Only Served & Not Served: Click on the downward arrow next to Served or Not is (All) and change the filter so that only Served & Not Served show.
         
   6. Suggested Formatting Changes (in the Format Pane):
      1. Add Border: Under Size and style, click Visual border to on.
      2. Title: Edit the text of the Title and change the font size, coloring, and positioning.
      3. X-axis: Turn Title off. Change the font of the Values (Years) under the Values subsection. Change the Type to Categorical to ensure that every year label appears.
      4. Y-axis: Turn Title off. Change the font and/or Display Units of the Values (count of cases) under the Values subsection.
      5. Legend: Turn Title off. Change the positioning and font.
      6. Small Multiples: Change the number of rows to 5 (or however many categories you allowed in the Top N filter).
      7. Columns: Change the colors for Served and Not Served categories under Apply settings to. You must select each Series (Served and Not Served) separately to change colors.
      8. Data Labels: Turn Data Labels on. Leave Apply setting to Series All. Turn Detail (under Data Labels) on. Change the font (you may have to make the font very small for it to show up in the visual) and change the Value decimal places to 1.
         
      9. Data Format: In the Properties section of the Format Pane, under Data format, change Apply settings to Count of Matter/Case ID, change Format to Whole Number and turn on the Thousand separator. Doing this will show the numbers in easier to read format when viewers hover over any point on the chart to see the Tooltips information.
         
      10. Category Orders: To have Served appear lowest in each column, click on the three-dot ellipsis at the top right corner of the chart to open up More Options. Under Sort legend, select Sort descending. To have the Problem Code Categories appear in descending order by case volume, click on the three-dot ellipsis at the top right corner of the chart to open up More Options. Under Sort small multiples, select Count of Matter/Case ID and Sort descending.