BI for Beginners (service version) Session 4

Power BI
beginner
Published

October 3, 2024

Session materials

Ideally, you’ll use the data that you wrangled during the previous session. But if that’s not suitable, use this data instead:

No feedback found for this session

Session outline

  • In this session, we’re going to build out the GP practice size dashboard from last time
  • we’re going to use some new features of Power BI to help us with that
    • we’ll spend a little bit of time looking at the model
    • but most of the session is about DAX and measures

Recap

  • we did lots of data wrangling last week using Power Query
  • that’s designed to do data loading and tidying - so we tried removing some data, reshaping data, and joining data,…
  • but there’s another whole language in Power BI to let us do things with that tidy data: DAX (annoyingly, Data Analysis eXpressions)
  • similar to Excel formulas

Major differences between DAX and Excel formulas

  • Excel takes a cell or range of cells (like A4)
    • DAX functions take a column or table as reference, and cannot use e.g. cell ranges
  • Excel formulas show their results directly in the cell
    • DAX formulas need to be added to a card (or similar)
  • also a grab-bag of minor differences (no mixed columns data type, date and time functions, different lookup mechanics)

Set up

Task
  1. open your dashboard from the last session
  2. add a new report page, and name it measures
  3. remind yourself about the data we’re working with

The model

  • let’s start by looking at the model view
  • we should see our data tables as blocks in the model
  • there are also connections between some of those blocks
Task
  1. Click Open data model Open the data model
  2. That should open the data model in a new tab data model tab
  3. Try expanding the Properties pane

DAX first steps

Task
  1. in the model view, click New measure
  2. in the formula bar, add largest_practice = MAX(practices[PracticeListSize])

DAX queries

The general way of building a DAX formula is as follows:

measure = FUNCTION(table[column])

  • note the [square brackets] for column names
  • we wrote a simple DAX formula in the first session, when we calculated a column using the formula AvailableBeds = [BedComp] - [ClosedBed]
  • in this session, we’re going to concentrate on using DAX to write measures

WTH are measures?

  • measures are Power BI lingo for calculated data summaries
  • calculated columns, as we’ve seen before, let you make new columns from existing data
  • measures allow you to make non-column summaries of your data - like an average

DAX queries

For our example, our measure is called largest_practice, and we want it to report the largest practice in each health board. Our DAX-based measure works as follows:

  • our data table is called practices
  • our column is PracticeListSize
  • we’re using the MAX() function to find the largest value (exactly like Excel)

largest_practice = MAX(practices[PracticeListSize])

Adding our measure to our report

  • we should be able to add our largest_practice measure to our report
  • switch back to the report tab
  • you should see the largest_practice measure appear in your practices data largest practices measure
Task
  1. add a new card new card
  2. in our new measures page, drag the largest_practice measure into that card
  3. we also want to be able to look at different boards, so add a slicer new slicer using the HBnames from the practices table

Concatenating

  • so far, our largest_practice measure is ugly-but-functional
  • we can use the concatenate operator (&) to beautify it
Task
  1. edit your largest_practice measure (go to the model view and select the measure in the data pane)edit largest practice
  2. you can insert text by “quoting” it, and using & to join it to the result of your MAX()
    • for instance, largest_practice = "Largest practice: " & max(practices[PracticeListSize]) & " patients"
  3. go back to the report, and refresh the report refresh the report
  4. you can also use the format visual section to change the font size, and drop/alter the category label

Core DAX functions

  • several useful DAX functions work in basically the same way as MAX()
  • let’s try them out now
Task
  1. make a new measure called average_practice
  2. try average_practice = AVERAGE(practices[PracticeListSize])
  3. add this to a new card to test it
  4. now tweak with concatenated text
  5. COUNT(), and MIN() should also work in the same way

Combining functions

  • most measures will need more than one DAX function
  • we combine functions just like Excel, by bracketing them together
  • function1(function2(data)) does function 2 on the data, then function 1 on that result
  • when you’re reading/building formulas with many functions, two top-tips:
    1. make sure you match your brackets
    2. read from the inside out

Beautifying measures

  • our average_practice measure is pretty ugly
  • a whole number value would be more suitable
Task
  • click on the average_practice measure, which should read average_practice = AVERAGE(practices[PracticeListSize])
  • add the INT() function, which converts a number to an integer (whole number)
    • we want INT() to wrap around the AVERAGE() function
    • average_practice = INT(AVERAGE(practices[PracticeListSize]))

Combining functions for number formatting

  • alternatively, we could round that result using round
  • average_practice = ROUND(AVERAGE(practices[PracticeListSize]), -2)
    • the -2 specifies the number of decimal places
  • care with the brackets!
  • you might prefer to do simple rounding in the format visual area Format visual area

Filter

  • FILTER() is the last DAX function we’re going to look at
  • it lets us filter our data based on conditions
  • say we want to count how many big practices (more than 10000 patients) we have…
  • big_practices = countrows(filter(practices, practices[PracticeListSize] > 10000))
  • let’s read this from the inside-out
    • filter(practices, practices[PracticeListSize] > 10000) applies the filter function over the whole practices table
    • it finds all the rows where PracticeListSize is greater than (>) 10000
    • those rows are then counted by countrows()
    • finally, we save that number into our measure named big_practices

Beautifying our measures

  • that’ll be another ugly-but-functional measure
  • we’ll use an alternative way of beautifying this one, to avoid making the formula too complicated
  • measures can contain measures
  • instead of the table[column] format we’ve been using, we refer to measures by wrapping them in single square-brackets: [measure]
  • let’s create this now
Task
  1. add a new measure with the formula big_practices = countrows(filter(practices, practices[PracticeListSize] > 10000)) to obtain the filtered count of big practices
  2. create a second new measure
  3. nice_big_practices = [big_practices] & " practices with more than 10000 patients"
  4. add nice_big_practices to a new card
Task
  1. Please make a new measure named nice_average_practice
  2. Use concatenation to make the average_practice measure nicer to read
  3. Update your average measure card in the report

More interesting filters

  • FILTER() is one of the big building-blocks of Power BI
  • we can use it as a way of looking up matching data
  • for example, to find the name of the largest practice, we can:
largest_practice_name = CALCULATE (
    VALUES (practices[Practice Name]),
    FILTER (
      ALL(practices[PracticeListSize]), 
      practices[PracticeListSize] = MAX (practices[PracticeListSize]
            )
            )
)
  • FILTER() finds the largest practice
  • VALUES() returns the corresponding value from the Practice Name column
  • CALCULATE() joins the FILTER() and VALUES() parts of the expression

DAX resources (beginners)

DAX resources (advanced)