PowerBI.tips

Tag: DAX Blank

  • Crazy Table Gymnastics – Part 2 – Build Support Materials

    Crazy Table Gymnastics – Part 2 – Build Support Materials

    This is part 2 in the 3 part series on developing super cool tables using some fancy measures.  In part 1 we walked through how to build a table that uses an un-pivoted data source in the Query Editor.  This technique allows you to change the types of categorical values in a table.  If you missed part 1 and want to get caught up follow this link.  Now, continuing with the series, Part 2, we will build the supporting materials (Selector Table, What If Slicers, and measures) for the report.

    Once we are done the final product will look like the following:

    Part 2… Go.

    To make sure we are starting off on the correct step.  We left off part 1 when we had completed a Pivoted Data Table and included an Attribute Slicer that would allow us to toggle between the Manager and Region Categories.  Your table should look like the following diagram:  (If you don’t have this you might want to start with Part 1 found here)

    Pivoted Data Table
    Pivoted Data Table

    Note: I have also included a Slicer which is used with the Attribute field.

    Next, we will need to add a table that will allow us to use the SalesReps, PercentChange, and Margin column headers in our report.  On the Home ribbon click Edit Queries, then select New Source on the Home ribbon.  In the Get Data window select Blank Query, click Connect to proceed.  Open the advanced editor by clicking the Advanced Editor button found on the Home ribbon.  Enter the following M code into the Advanced Editor:

    let
   Source = #"Pivoted Data",
   #"Kept First Rows" = Table.FirstN(Source,1),
   #"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Attribute", "Value", "UniqueID"}),
   #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
   #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"})
in
   #"Removed Columns1"

    Click Done to close the Advanced Editor.  Rename the table to Selector.  When you are finished your table should look like the following:

    Create Selector Table
    Create Selector Table

    Click Close & Apply on the Home ribbon to close the Query Editor.  Add a slicer with the following selections:

    Add Selector Attribute Slicer
    Add Selector Attribute Slicer

    Now, we want to detect which of the Attributes have been selected from this table.  We can accomplish this by creating a measure using the DAX function SELECTEDVALUE.  Right Click on the table named Selector and from the drop down select New Measure.  Enter the following DAX equation:

    rankBy = SELECTEDVALUE(Selector[Attribute])

    In addition to the knowledge of which column was selected from the selector table, we will also want to detect to make sure at least one categorical value has been selected.  The categorical values we are talking about were generated earlier.  The values could be either the Manager or Region values of the Attribute column in the Pivoted Data table.  Using the ISFILTERED DAX function enables this section.  Add the following measure to the Pivoted Data table:

    Attribute Filtered = ISFILTERED('Pivoted Data'[Attribute])

    Next, we need to gather some user input in the form of a number from 1 to 10.  To input this information we need to produce a What If Parameter.  On the Modeling ribbon click New Parameter in the What If section of the ribbon.  Enter the following information into the What-if parameter dialog box:

    What If Parameter
    What If Parameter

    Note: Don’t forget to change the Name of the parameter.

    Click OK to proceed.  Power BI will automatically produce a measure table, a measure and a slicer on the report page.

    Slicer Produced by What-if
    Slicer Produced by What-if

    Note: By default there is nothing selected in the box.  However, you can adjust the slicer and a number will appear within the value box.  You can also type in a number between 1 and 10 to the box to adjust the value.

    This is where we go crazy with DAX.  This portion of DAX is where all the magic happens.

    We start off by building our totals measures.  Place all these measures in the Pivoted Data table.

    Total % Change = MAX( 'Pivoted Data'[PercentChange] )
Total Margin = SUM( 'Pivoted Data'[Margin] )
Total SalesReps = SUM( 'Pivoted Data'[SalesReps] )

    These will be used repeatedly in our next group of DAX formulas.

    The following measures will produce a calculated ranking for each numerical column.  OK, Pause, This part really excites me here because the next few measures are where the magic happens.  Pay close attention to what is happening here.  Un-Pause, by using the DAX Switch function we can dynamically tell Power BI to adjust which column we want to see ranked by the top items.  For example, if we select SalesReps in our attribute slicer.  The following measures will automatically rank all the items in the table by the column named SalesReps.  Thus, the items with the highest counts of SalesReps will be listed first.  When you select Margin, the table will automatically adjust and re-rank the items by the Margin column.  This is being done in the switch statement.  For each column we are calculating custom rankings and then hiding or replacing values with the Blank() DAX function to not show items we don’t want.

    Enter the following three measures into the Pivoted Data Table:

    Rank Margin = if( [Attribute Filtered],
    SWITCH( [rankBy]
        ,"Margin", CALCULATE( [Total Margin], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total Margin]) <= [Top # of Items Value]) )
        ,"PercentChange", CALCULATE( [Total Margin], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total % Change]) <= [Top # of Items Value]))
        ,"SalesReps", CALCULATE( [Total Margin], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]),[Total SalesReps]) <= [Top # of Items Value]))
    )
    , BLANK() )

Rank PercentChange = if( [Attribute Filtered],
     SWITCH( [rankBy],
       "PercentChange", CALCULATE( [Total % Change], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total % Change]) <= [Top # of Items Value]))
       ,"Margin", CALCULATE( [Total % Change], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total Margin]) <= [Top # of Items Value]))
       ,"SalesReps", CALCULATE( [Total % Change], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total SalesReps]) <= [Top # of Items Value]))
    )
    , BLANK() )

Rank SalesReps = if( [Attribute Filtered],
    SWITCH( [rankBy]
        ,"SalesReps", CALCULATE( [Total SalesReps], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total SalesReps]) <= [Top # of Items Value]))
        ,"Margin", CALCULATE( [Total SalesReps], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total Margin]) <= [Top # of Items Value]))
        ,"PercentChange", CALCULATE( [Total SalesReps], FILTER('Pivoted Data', RANKX( ALLEXCEPT('Pivoted Data','Pivoted Data'[Value]), [Total % Change]) <= [Top # of Items Value]))
    )
    , BLANK() )

    Whew, that was a ton of measures.  All the key components are complete now.  In part 3 we will clean up our report page and make it shine.  I hope you enjoyed this tutorial. Also, follow me on Twitter and Linkedin where I will post all the announcements for new tutorials and content.

    Linkedin Twitter
  • Using Variables within DAX

    Using Variables within DAX

    Every so often you find a hidden gem, something so valuable, just hiding there in plain sight.  Recently, I found out that I LOVE the using Variables with in a DAX expression.  Ok, brief introduction, I was trying to calculate sales changes between different years.  The data looked similar to the following table:

    Year Month Sales
    2013 1 20
    2013 2 25
    2014 1 22
    2014 2 23
    2015 1 21
    2015 2 31

    I wanted to sum the sales for each year and compare the percent change between each year.  If you have followed my blog for a while, I have a ton of tutorials about percent change.  ( See list of other tutorials dealing with percent change )

    Now, for the twist making this calculation more difficult.  I want to calculate percent change even when a filter is applied by clicking on a visual.  By using variables we can apply some data modeling voodoo magic to get the desired results.  See a sample of the measures working in the Power BI Embedded sample below:

    Let’s dive in!

    Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query.  Click Connect to open the Query Editor.  On the View ribbon click the Advanced Editor button.  While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

    Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor.  This tutorial teaches you how to copy and paste M code into the Advanced Editor.

    let
 Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2017/05/Clothing-Sales.xlsx"), null, true),
 ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}}),
 #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number)
in
 #"Inserted Year"

    Your loaded data should look like the following:

    Load ClothingSales Data
    Load ClothingSales Data

    Rename the Query to ClothingSales, and click Close & Apply to load the data into the data model.

    It is a good practice to review the data columns and verify that the formatting is set up correctly.  To do this click on the the column Date in the Fields pane, then click on the Modeling ribbon.  In the Formatting section change the format to Date Time and select M/d/yyyy format.  Repeat this for the following columns:

    • Format Sales to Currency then select $ English (United States)
    • Format Year to Whole Number

    With our data properly formatted we are ready to build a simple bar chart.  See the image below for the bar chart set up:

    Create Bar Chart
    Create Bar Chart

    To start we will create a explicit calculation for the total selected sales.  On the Home ribbon click the New Measure button and enter the following code:

    Total Selected Sales = SUM(ClothingSales[Sales])

    Pro Tip: To learn more about explicit vs implicit calculations within Power BI Click the following link.

    Add a Card visual and add our new measure.

    Add Card Visual
    Add Card Visual

    Let’s change the formatting measure so we can see the full number.  With the Card still selected click the Paint Roller expand the section labeled Data label and change the units from Auto to None.  This allows us to see the entire sales number.

    Remove Number Formatting from Card
    Remove Number Formatting from Card

    Let’s enhance this card visual further by adding dollars formatting.  Make sure you have the Total Selected Sales measure selected by Clicking on the words of the measure.  Then, click on the Modeling ribbon click and change the Format to Currency, then select $ English (United States). The final format of the card visual should look like the image below:

    Final Card Format
    Final Card Format

    For the next step we will repeat the previous steps for our new measures.

    1. Create the measure
    2. Change the formatting of the card
    3. Change the formatting of the measure

    The next measure will calculate the prior year total sales, but only calculate this value when a filter context from a different visual is applied.  Take note this is the magic I was talking about!!

    Prior Year Sales = 
VAR 
selectedYear = if( HASONEVALUE(ClothingSales[Year]), 
    FIRSTNONBLANK(ClothingSales[Year],ClothingSales[Year]),
    BLANK() )-1
Return 
    CALCULATE(
        SUM(ClothingSales[Sales]),
        ALL(ClothingSales),
        ClothingSales[Year] = selectedYear)

    I am going to pause a second here and explain what is going on in this measure as we have multiple things going on.  First, we are creating a variable with the VAR.  In the below image I show you that the variable that we are defining is called selectedYear.  SelectedYear returns a value of blank if multiple years are selected.  This is accomplished by using the if statement and the HASONEVALUE() DAX formula.  The Return in then allows us to output a value.  In this case we are using the CALCULATE() to sum all the sales.  The next part is crucial to making this work.  Within the Calculate we are applying two filters.  The first filter selects the entire table with the All() then we apply the filter from the earlier defined variable with the name of selectedYear.

    Prior Year Sales in Color
    Prior Year Sales in Color

    Apply the same formatting steps 2, and 3 mentioned earlier.  Now, we can select one of the years within our bar chart.  Doing so populates the two measures we created.  The selected year total sales, and the prior year of sales. I selected the year of 2014, which has sales of $11k.  Hover your mouse over the bar for year 2013 and the tool tip will appear showing a total sales of $10K.

    Selected Bar
    Selected Bar

    Now we will make our percent change measure.  Enter the following as a new measure:

    Percent Change = DIVIDE([Total Selected Sales],[Prior Year Sales],1)-1

    Pro Tip: Use the DAX function DIVIDE when dividing two numbers, this protects against odd cases where the denominator is zero.

    Click on the Modeling ribbon and change the formatting of the measure to Percentage.  Add a final measure that calculates the difference.  See measure below:

    Prior Year Delta = [Total Selected Sales] - if( [Prior Year Sales] = BLANK(), [Total Selected Sales],[Prior Year Sales])

    This measure calculates the total changes in sales between the two selected years.  The if statement at the end of the equation handles a special case that if you don’t have any individual year selected the prior year delta defaults to zero.

    Thanks for following along an learning about variables.  Trust me, these are super helpful.  Make sure you spend some learning how to leverage variables in your DAX equations.

    This Tutorial was inspired by the following article I found from SQLBI authored by the master of DAX Alberto Ferrari.  I mean, come on, Ferrari, how baller is that last name!  I guess that is why he is the master.  Kudos to you Alberto!

    Still need more reading about DAX, check out the following books: