PowerBI.tips

Month: July 2016

  • Measures – Intelligent Card Visual – Using DAX

    Measures – Intelligent Card Visual – Using DAX

    As I have been exploring PowerBI and building dashboards I have noticed that often the visuals can obscure your data.  As you click on different visuals there is a need to highlight different pieces of data.  Take for example the following dashboard:

    Sample Visual Example
    Sample Visual Example

    Notice the different car types in the bar chart.  As you click on each vehicle type, Diesel, Hatchback, etc.. you expect the data to change accordingly.  In some cases it is helpful to present a card visual to show the user what you selected and any relevant data points you want to highlight.  For example if I select the Diesel vehicle type I may want to know the average sales amount, total sales in dollars, or number of units sold.  This is where we can build specific measures that will intelligently highlight selected data within your PowerBI visual.

    Here is a sample of what we will be building today:

    lets begin with starting with some data.  In honor of your news feed being bombarded with Pokemon Go articles lets enter some data on Pokemon characters.

    We will enter our data manually.  For a full tutorial on manually entering in data visit here.

    Click the Enter Data button on the Home ribbon and enter the following information into the displayed table.

    Pokemon XP
    Pikachu 1200
    Weedle 650
    Pidgey 800
    Golbat 300

    Rename the table to Characters.  Once you are finished entering in the data it should look like the following:

    Create Table of Characters
    Create Table of Characters

    Click Load to continue.

    Start to examine your data by building a table visual.

    Table Visual
    Table Visual

    Next add a Bar chart.

    Bar Chart
    Bar Chart

    Note: I added the XP column twice.  Once to the Value attribute and to the Color Saturation.  This enhances the look of your visual by coloring the bars with a gradient.  The largest bar will have the darkest color, and the smallest bar will have the lightest color.

    Next, we will begin building some measures.  The first measure will be a total of all the experience points (XP) for each character. Click the New Measure button on the Home ribbon and enter the following DAX expression:

    Total XP = Sum(Characters[XP])

    Now, add a Card visual and add the new measure we created Total XP.

    Total XP Card Visual
    Total XP Card Visual

    This measure totals all the experience points for all the selected characters within the visual.  Since all characters are now selected the total XP for all characters is 2,950.

    The next, and final measure, will be the intelligent card.  For this measure we want to display the characters name when we select them in the bar chart.  Click the New Measure button on the Home ribbon and enter the following DAX expression:

    Character(s) = IF( DISTINCTCOUNT(Characters[Pokemon]) = 1 , FIRSTNONBLANK('Characters'[Pokemon],'Characters'[Pokemon]) , DISTINCTCOUNT('Characters'[Pokemon]) & " Selected")

    Update: As of Mid 2017 Microsoft introduced a new DAX expression called SELECTEDVALUE which greatly simplifies this equation.  Below is an example of how you would change the DAX equation to use SELECTEDVALUE.

    Selected = SELECTEDVALUE(  Characters[Pokemon],  DISTINCTCOUNT(  Characters[Pokemon]  )  &  " Selected" )

    Explanation of this measure:

    This measure first checks to see how many distinct items are in the column Pokemon of our dataset.  If there is only one selected character then we will display the FIRSTNONBLANK character, which will be the name of our selected character.  If there are more than one characters selected. The measure will count the number of characters selected and return a text string with the count and the word Selected. Thus, showing us how many items have been selected.

    Add the measure titled Character(s) to a card visual.

    Add Character Card Visual
    Add Character Card Visual

    We can now see that there are 4 characters selected.  Clicking on Pikachu in the bar chart resolves with the character’s name being displayed and the XP of Pikachu being displayed in the Total XP card visual.

    Selecting Pikachu
    Selecting Pikachu

    You can select multiple items by holding down Ctrl and clicking multiple items in the bar chart.

    Selecting Pikachu and Pidgey

    Well, that is it.  I hope you enjoyed this Pokemon themed tutorial.  Thanks for visiting.

    Want to learn more about PowerBI and Using DAX.  Check out this great book from Rob Collie talking the power of DAX.  The book covers topics applicable for both PowerBI and Power Pivot inside excel.  I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.

  • Measures – Month to Month Percent Change

    Measures – Month to Month Percent Change

    I had an interesting comment come up in conversation about how to calculate a percent change within a time series data set.  For this instance we have data of employee badges that have been scanned into a building by date.  Thus, there is a list of Badge IDs and date fields.  See Example of data below:

    Employee ID and Dates
    Employee ID and Dates

    Looking at this data I may want to understand an which employees and when do they scan into a building over time.  Breaking this down further I may want to review Q1 of 2014 to Q1 of 2015 to see if the employee’s attendance increased or decreased.

    Here is the raw data we will be working with, Employee IDs Raw Data.  Our first step is to Load this data into PowerBI.  I have already generated the Advanced Editor query to load this file.  You can use the following code to load the Employee ID data:

    let
 Source = Csv.Document(File.Contents("C:\Users\Mike\Desktop\Employee IDs.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 #"Promoted Headers" = Table.PromoteHeaders(Source),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Date", type date}}),
 #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
 #"Calculated Start of Month" = Table.TransformColumns(#"Sorted Rows1",{{"Date", Date.StartOfMonth, type date}}),
 #"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Date"}, {{"Scans", each List.Sum([Employee ID]), type number}})
in
 #"Grouped Rows"

    Note: I have highlighted Mike in red because this is custom to my computer, thus, when you’re using this code you will want to change the file location for your computer.  For this example I extracted the Employee ID.csv file to my desktop. For more help on using the advanced editor reference this tutorial on how to open the advance editor and change the code, located here. 

    Next name the query Employee IDs, then Close & Apply on the Home ribbon to load the data.

    Close and Apply
    Close and Apply

    Next we will build a series of measures that will calculate our time ranges which we will use to calculate our Percent Change (% Change) from month to month.

     

    Now build the following measures:

    Total Scans, sums up the total numbers of badge scans.

    Total Scans = SUM('Employee IDs'[Scans])

    Prior Month Scans, calculates the sum of all scans from the prior month.  Note we use the PreviousMonth() DAX formula.

    Prior Month Scans = CALCULATE([Total Scans], PREVIOUSMONTH('Employee IDs'[Date]))

    Finally we calculate the % change between the actual month, and the previous month with the % Change measure.

    % Change = DIVIDE([Total Scans], [Prior Month Scans], blank())-1

    Completing the new measures your Fields list should look like the following:

    New Measures Created
    New Measures Created

    Now we are ready to build some visuals.  First we will build a table like the following to show you how the data is being calculated in our measures.

    Table of Dates
    Table of Dates

    When we first add the Date field to the chart we have a list of dates by Year, Quarter, Month, and Day.  This is not what we want.  Rather we would like to just see the actual date values.  To change this click the down arrow next to the field labeled Date and then select from the drop down the Date field.  This will change the date field to be viewed as an actual date and not a date hierarchy.

    Change from Date Hierarchy
    Change from Date Hierarchy

    Now add the Total Scans, Prior Month Scans, and % Change measures.  Your table should now look like the following:

    Date Table
    Date Table

    The column that has % Change does not look right, so highlight the measure called % Change and on the Modeling ribbon change the Format to Percentage.

    Change Percentage Format
    Change Percentage Format

    Finally now note what is happening in the table with the counts totaled next to each other.

    Final Table
    Final Table

    Now adding a Bar chart will yield the following.  Add the proper fields to the visual.  When your done your chart should look like the following:

    Add Bar Chart
    Add Bar Chart

    To add a bit of flair to the chart you can select the Properties button on the Visualizations pane.  Open the Data Colors section change the minimum color to red, the maximum color to green and then type the numbers in the Min, Center and Max.

    Changing Bar Chart Colors
    Changing Bar Chart Colors

    Well, that is it, Thanks for stopping by.  Make sure to share if you like what you see.  Till next week.

    Want to learn more about PowerBI and Using DAX.  Check out this great book from Rob Collie talking the power of DAX.  The book covers topics applicable for both PowerBI and Power Pivot inside excel.  I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.