Data Analysis Tool For All

Closed Case 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: Close 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. And cases with Rejection Reasons entered or with Close Reasons indicating no service provided (ex: R-Rejected, X-Error).
  • Include: Cases that were closed with service (not rejected).
  • 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 your Power BI file.
5. 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: ‘Closed Cases 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. Click on the green check box next to the formula bar to verify the formulas are working and to create the new columns.
      4. Add these measures before creating visuals.
   2. Race Formula:
      Race Formula =
      SWITCH(
      TRUE(),
      ‘Closed Cases 2020-2024'[Race] = "Black or African American", "Black", ‘Closed Cases 2020-2024'[Race] = "American Indian or Alaska Native", "AIAN",
      OR(‘Closed Cases 2020-2024'[Race] = "Other", ‘Closed Cases 2020-2024'[Race] = "Native Hawaiian or other Pacific Islander"), "Other",
      OR(‘Closed Cases 2020-2024'[Race] = "", ‘Closed Cases 2020-2024'[Race] = "Prefer not to disclose"), "Unknown",
      ‘Closed Cases 2020-2024'[Race])
       
   3. Ethnicity Formula (for organizations that also have a HUD Ethnicity field):
      Ethnicity Formula =
      IF ( OR ( 'Closed Cases 2020-2024'[Ethnicity] = "Hispanic",
      'Closed Cases 2020-2024'[HUD 9902 Ethnicity] = "Latino/Hispanic" ), "Hispanic/Latino",
      IF ( OR ( 'Closed Cases 2020-2024'[Ethnicity] = "Non-Hispanic",
      'Closed Cases 2020-2024'[HUD 9902 Ethnicity] = "Non-Latino/Non-Hispanic" ),
      "Non-Hispanic/Non-Latino",
      IF ( OR ( AND ( 'Closed Cases 2020-2024'[Ethnicity] = ""
      'Closed Cases 2020-2024'[HUD 9902 Ethnicity] = "" ),
      'Closed Cases 2020-2024'[HUD 9902 Ethnicity] = "Chose not to respond" ), "Unknown",
      IF ( 'Closed Cases 2020-2024'[HUD 9902 Ethnicity] <> "",
      'Closed Cases 2020-2024'[HUD 9902 Ethnicity],
   4. Race & Ethnicity Formula:
      Race & Ethnicity = IF ('Closed Cases 2020-2024'[Ethnicity Formula] = "Hispanic/Latino", 
      "Hispanic/Latino", 
      IF ( OR ('Closed Cases 2020-2024'[Ethnicity Formula] = "", 
      'Closed Cases 2020-2024'[Ethnicity Formula] = "Non-Hispanic/Non-Latino"), 
      'Closed Cases 2020-2024'[Race Formula], 
      BLANK() ))
      i.This formula combines the previous two to pull all Hispanic/Latino clients of any race into one category and all Non-Hispanic/Non-Latino clients into their Race categories. It allows for comparison of race and ethnicity in one visual.
   5. Levels of Service:
      Levels of Service =
      IF ( LEFT('Closed Cases 2020-2024'[Close Reason], 1) IN {"A", "B"}, "Advice/Limited",
      IF ( LEFT('Closed Cases 2020-2024'[Close Reason], 1) IN {"F", "G", "H", "I", "K", "L"},
      "Extended",
      BLANK() ))
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 Close Date 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 Race & Ethnicity to Chart: Drag the newly created Race & Ethnicity column data into the Legend Box in the Build Pane.
      4. Adding Levels of Service to Chart: Drag the newly created Levels of Service column data into the Small Multiples box in the Build Pane.
         
      5. Change Filters (in the Filter Pane):
         1. Excluding Blank & Unknown Race & Ethnicity: Click on the downward arrow next to Race & Ethnicity is (All) and change the filter so neither Blank nor Unknown are not included.
         2. Show Only Advice/Limited & Extended: Levels of Service is (All) and change the filter so that only Advice/Limited & Extended 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. Note that when the volume is different (as it likely will be between Advice/Limited Service cases and Extended Service cases, you may want the line charts to have different Y-axis ranges. Under Y-axis-Range, turn Shared y-axis Off and turn Scale to fit on.
            
         5. Legend: Turn Title off. Change the positioning and font.
         6. Small Multiples: Change the number of rows to 1.
         7. Lines: You may change the line style, width, color for each line by selecting each Race & Ethnicity category in the Apply setting to Series drop down and making selections.
         8. 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.
            
         9. Legend Order: To sort the Race & Ethnicity categories in the legend in descending order, 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.
            
   5. 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 Close Date 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 Race & Ethnicity to Chart: Drag the newly created Race & Ethnicity column data into the Legend Box in the Build Pane.
      4. Adding Levels of Service to Chart: Drag the newly created Levels of Service column data into the Small Multiples box in the Build Pane.
         
      5. Change Filters (in the Filter Pane):
         1. Top 3 Race & Ethnicity Categories Excluding Blanks & Unknown: Click on the downward arrow next to Race & Ethnicity is (All) and change the filter so neither Blank nor Unknown are not included. Drag the Race & Ethnicity field over into another Add data fields here box in the Filter Pane under Filters on this visual. Change the Filter Type to Top 3 and enter Matter/Case ID in the By value box, changing it to Count (Distinct). Click on Apply Filter.
            
         2. Show Only Advice/Limited & Extended: Levels of Service is (All) and change the filter so that only Advice/Limited & Extended 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 3 (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 (Advice/Limited and Extended) 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 Extended 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 Race & Ethnicity 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.