DAX – Filtering Measure to show value when selected or no values selected

I had a requirement where I had a measure that I wanted to be filtered if it’s value was selected
then it must show the value. As well as if nothing is selected on the slicer then I also
want the value to be displayed.

I did this so that when the users are using the report, it will clearly show them what values they are looking at in the table. By making it clearly visible
once they selected a value from a slicer it is easier for the end users to get the right data.

This is best shown with an example below.

  • Current behavior is when I click on Day Shift it shows me the values for the Day Shift.
    • NOTE: In the above the 24hr Total does
      change to correctly reflect the Day Shift values.
  • Now when I click on Night Shift, I still see the values for Day Shift
    • NOTE: In the above the 24hr Total does change to correctly reflect the Night Shift values.

So below is the DAX that I used to overcome this challenge so that it would display correctly.

  • Here is the syntax with an explanation afterwards
    Day Shift =
    IF (
      ISFILTERED ( 'Prod'[Shift] )
      && VALUES ( 'Prod'[Shift] ) = "Day Shift"
      || NOT ( ISFILTERED ( 'Prod'[Shift] ) ),
     CALCULATE ( [UnitType (Selected)], 'Prod'[Shift] = "Day Shift" ),
    0
    )

  • What is happening above is I have got an IF Statement and what I am doing is first to see if the column is being filtered. This uses the ISFILTERED and is highlighted in PURPLE above.
  • Next is where I am also looking to see if the “Day Shift” has been selected from the Slicer, this is highlighted in ORANGE
  • I put in an OR “||” Condition, and I used the NOT which is then saying it is NOT FILTERED, this is so that if neitherDay Shift” or “Night Shift” is selected then also make this part of the IF Statement TRUE, this is highlighted in GREEN
  • Now that I had completed the Condition for my IF statement I then put in my calculation if it is TRUE, which is highlighted in BLUE
  • And then finally if the Slicer has “Night Shift” selected make the value for “Day Shift” equal 0

So it is easier to see it in action as shown below.

  • Here is showing where nothing is selected on the Slicers it shows all the values.
  • Next if I selectDay Shift” I want it to show the values for Day Shift, but make “Night Shift” zero. As well as the 24hr Total should only show the Day Shift values.
  • And finally, if I selectNight Shift” I want it to show the values for Night Shift, but make “Day Shiftzero. As well as the 24hr Total should only show the Day Shift values.

Conclusion

As you can see above I have demonstrated how to leverage DAX measures so that it will display the values in a way that is meaningful to the end users of the report.

This will help them easily understand the data that they are looking at, and not have to second question or double check to see if the figures are the ones that they are expecting to see.

Power BI – Dynamically changing the Title based on your selection

Would it not be nice if your Title changed based on what you selected on your slicer?

In this example below I am going to show you how I used a Country slicer to dynamically change the title of my card based on my slicer selections.

This was really useful because the Country Slicer has so many options I am not able to view all the Countries selected on the screen. And by doing it in this way it allowed me to create a more meaningful dashboard, as well as know which Countries had been selected.

As you can see below, this is what it looked like after I had added in my DAX measure and configured the Card. You can click on the Country Slicer to see the changes.

(more…)

Power BI – DAX Measures for Excel based “ % of Column Total “ or “ % of Row Total ”

Where I am currently consulting there was a requirement to create a measure like you can in the Excel pivot tables for the % of Column Total or the % of Row Total.

One of the things that you can currently do in Power BI, which I only learnt as part of this requirement is that you can use % of Column Total, or % of Row Total when using a table, as seen below.

But the thing is that as soon as you put that into a visual the only option that you have is the Percent of grand total.

So below are the two measures that I created so that I could then successfully have a % of Column Total (Percent of column total) or % of Row Total (Percent of row total)

(more…)

DAX – Getting the Previous Year to Date Value up to and including the Current Month Selected – Not the entire Previous Year

So for this example it is best explained with an image below, then some additional context afterwards

  • If you look above we have got the [Sum of Sales] for Dec 2010-11
    • NOTE: This I selected in the Slicer on the right hand side.
  • Then I have created a Year to Date (YTD) measure which is the running total for the Year
    • NOTE: This is the measure below.

      YTD-MTD-CY = TOTALYTD([Sum of Sales],’Date'[Calendar Date],”06/30″)

  • As you can see for Dec 2010-11 with the measure [YTD-MTD-CY] I have the Year to date value.

Now the requirement was if the user selected any Fiscal Month (EG: Feb) from the Slicer, they are looking for the Feb 2009-10 value for the Previous Year (Feb 2009-10). So once again if I show this in a picture this is the value that I am looking to get.

(more…)

BI-NSIGHT – Power BI (Granular Tenant settings, Amazon Redshift connectivity in Power BI Service, Featured Data Stories, Availability Monitoring Publish to Web) – Excel (Get & Transform updates) – SQL Server (DAX support in Report Builder and SSMS)

Here are the weekly updates.

Power BI – Granular Tenant Settings

I can personally say that I know a few of my clients who are very happy that the Power BI team has already implemented the Granular Tenant settings for Power BI. I have to say that I am once again impressed by the Power BI Team really a great job.

I also like the fact that they have gone one step further and put in the option for “Except specific security groups”, because so often you want to allow it for almost everyone, except one group. And with their implementation they make this really easy and simple to implement.

Currently as at 16 Mar 2017, the granular settings are for the following:

  • Export and sharing settings
    • Share content to external users
    • Export Data
    • Export reports to PowerPoint presentations
    • Print dashboards and reports
  • Content pack settings
    • Publish content packs to the entire organization
    • Create template organizational content packs
  • Integration settings
    • Use Analyze in Excel with on-premise datasets

You can read the blog post here: Announcing granular tenant settings in Power BI

Power BI – Gateway Update for March

As you can see above the Gateway for Power BI has had a whole host of updates in the March 2017 release.

In this release there is now an updated UI (User Interface), where you can now see the status of the Gateway.

You also have the options under the Service settings to restart the service, as well as change the Gateway Service Account.

In the Diagnostics section, you can now enable verbose logging, as well as export all the gateway logs to one zip file.

And finally under the Network section there is the capability to view the Local network status, as well as modify the Azure Service Bus connectivity mode.

There is also an update for the Gateway to use a FQDN instead of a list of IP addresses so that you will no longer be required to keep a list of IP Addresses.

Also if you are looking to use an Oracle data source with Kerberos configured the Power BI Gateway team are looking for candidates.

You can find the blog post details here: Power BI Gateways–March Update

Power BI – Amazon Redshift in Power BI Service

This is a informative blog post by the Power BI team where they explain how to connect, and publish your Power BI reports using Amazon Redshift.

You can find the details here: Announcing support for Amazon Redshift connectivity in the Power BI service

Power BI – Featured Data Stories

Congratulations to David Eldersveld on his featured data story, really a great way showcasing Power BI as well as the other people featured.

Next month’s feature requests are tables and matrices.

You can find the featured data stories here: Congratulations to this month’s Featured Data Stories

Excel – Get & Transform Updates

There are two updates to the Excel Get & Transform updates in the March 2017 update. The first is a new transformation which allows you to extract data values from a column. And the second is where you now have support for SQL Server Failovers when using a SQL Server database connection.

You can find the details here: March 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Availability Monitoring Publish to Web

Charles Sterling from the Power BI team has a great blog post if you want to monitor the availability and ensure that your Publish to Web is working. He shows this in a few simple steps.

You can find the details here: Setting up availability monitoring for Power BI Publish to Web Results

SQL Server – DAX support in Report Builder and SSMS

In the latest release of Report Builder and SSMS (SQL Server Management Studio) there now is the ability in the query designer for DAX.

You can find all the details here: Query designer support for DAX now available in Report Builder and SQL Server Data Tools