DAX

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

Gilbert Quevauvilliers September 5, 2017 No Comments

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 neither “Day 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 select “Day 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 select “Night Shift” I want it to show the values for Night Shift, but make “Day Shift” zero. 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

admin August 1, 2017 4 Comments

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 ”

Gilbert Quevauvilliers July 18, 2017 No Comments

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…)

BI-NSIGHT – Power BI (SSRS mid 2017, Mobile Apps Update, OkViz Custom Visuals Updates) – SQL Server vNext (Get Data Experience,

Gilbert Quevauvilliers December 22, 2016 No Comments

Whilst last week there was not a lot of news in BI, Microsoft has been releasing some information which does make it feel like Christmas in the area of BI!

Power BI – SSRS Mid 2017

This was really some welcome news especially for the people who use SQL Server Reporting Services (SSRS) and are looking to use the Power BI Capabilities for their On-Premise data.

From the blog post, it does appear that there will be a Technical Preview from what I can understand you will be able to download targeted for January 2017.

Along with that they are looking to start with adding features for Custom Visuals, additional data connectors and Power BI Mobile apps viewing.

And finally targeting the Production ready version in mid-2017, which is fantastic.

You can read the details here: Power BI reports in SQL Server Reporting Services: Feedback on the Technical Preview

Power BI – Mobile Apps Update

Just in time for the holiday period some nice updates to the Power BI Mobile app.

As with the image above you can now annotate and share what you want to explain via the iOS app a lot easier. As well as using a QR code to get access to dashboards.

Also they have added GeoFiltering for Android, Custom URL on an Image Tile for iOS and Android and they have also adjusted the offline background refresh so that it will consume less data.

You can find the details here: Power BI mobile apps feature summary – December 2016

OkViz – Custom Visual Updates

The guys from OkViz have released to new Custom Visuals which are the Candlestick and the Color Helper. I do think that often we forget about Color Blind people and this visual is a great way to assist them.

OkViz has also updated all their other existing Custom Visuals, which in the past were missing some key features such as tooltips in some of them.

You can find the custom visuals here: okviz.com

SQL Server vNext – Get Data Experience

This announcement really excites me, having worked in Power Pivot and then Power BI since its inception and now to see this coming into the next version of SQL Server is really amazing.

To me it is a natural progression, and having worked exclusively in Power BI for a few months now I have learnt a great deal, but also have found that things that were traditionally very hard to do in the past are now extremely easy or easy work arounds to shape and get your data in a format that makes for great analysis.

And to see this coming to SQL Server Analysis Services means that I can now leverage all my knowledge when this comes available. Which means that large organizations who have not been able to take advantage of this now can. As well as to have all the data hosted on premise for those that require it.

As I am sure you can understand it will start out with some limited functionality, but this is still a SQL Server vNext, and with each iteration more features will be added.

You can find all the details here: Introducing a Modern Get Data Experience for SQL Server vNext on Windows CTP 1.1 for Analysis Services

SQL Server vNext – CTP 1.1 now available

It is also great to see that there is another version for SQL Server vNext just before Christmas. And there are a whole host of updates.

Firstly details around what is coming in Analysis Services Tabular models, which I have highlighted above with the Get Data Experience, as well as drill-down to established data, which is often what people request. As well as ragged hierarchies and finally enhanced security for tabular models where they are looking to give you the ability to set permissions on individual tables and more granular security!

As well as enhancements for SQL Server on Windows and Linux, which makes it easier to use SQL Server, as well as no doubt perform better.

The blog post details are here: SQL Server next version Community Technology Preview 1.1 now available

Power BI (DAX) – Quick Tip – How to count how many items are selected on a Slicer

Gilbert Quevauvilliers December 14, 2016 No Comments

Update: 16 December 2016 (I got a comment from Marco Russo, who suggested that can use the FILTERS DAX Syntax which will “Return a table of the filter values applied directly to the specified column.” As well as this should also be faster. I am all about the speed!

I was working on a Power BI Project and one of the requirements for a rather complex DAX calculation was to know how many items the user had selected for the Fiscal Year in the Fiscal Year Slicer, which would then determine which Calculated Measure to use.

The trick here is that I wanted the count to be shown correctly in the Row Context. So that the count of items selected in the Slicer is on EVERY row.

What I mean by this is you can very easily get a count by using a Distinct Count which I can show below.

As you can see above it is counting it once for each row, which is correct when using the Distinct Count.

But what I was required to do, was to have the 8 being shown on every row. But if I deselected Fiscal Year 2011, I wanted it to change to 7 across the rows.

I think that this is something that will be really useful as well as allow me to use the count to dynamically use Calculated Measures.

Example:

  • I am going to be using my Fiscal Date from my Date Table as a Slicer.
  • I am then going to get a count of the rows, and show the total rows on each row.

Solution

Below is the DAX Syntax as well as an explanation of what it is doing.

  • Original DAX Syntax

    ZCALC – Fiscal Year V1 =

    CALCULATE (

    CALCULATE (

    DISTINCTCOUNT ( ‘Date'[Fiscal Year] ),

    ALLSELECTED ( ‘Date'[Fiscal Year] )

    ),

    ALLSELECTED ( ‘Date’ )

    )

  • Updated and Improved DAX Syntax

    ZCALC – Fiscal Year V2 =

    CALCULATE (

    COUNTROWS ( FILTERS ( ‘Date'[Fiscal Year] ) ),

    ALLSELECTED ( ‘Date’ )

    )

  • The trick here is that I first get a count of the rows based on what has been filtered in my ‘Date'[Fiscal Year] column highlighted in LIGHT BLUE

    COUNTROWS ( FILTERS ( ‘Date'[Fiscal Year] ) ),

    • So what I am doing here is to get the count of rows for what has been Filtered.
  • Then what happens with the outer CALCULATE is that it now takes the totals and breaks the Date Filter Context on the rows and using the ALLSELECTED applies it to each row.
  • So what you now get is the following when all the dates are selected for 8 years
  • And then if I have to deselect Fiscal Year 2011, which now makes it 7 Years selected I now get 7 in each row

As you can see this is very handy and can be used a variety of ways.

DAX for your data

If you want to apply this DAX pattern to your data this is how you can achieve it.

If you have all your data in one table called “My Data” and you have a slicer called “Type” you can change the DAX syntax to the following.

Type Slicer Count =

CALCULATE (


COUNTROWS ( FILTERS ( ‘My Data‘[Type] ) ),

ALLSELECTED ( ‘My Data‘ )

)

        

  • What you need is to change the COUNTROWS to be your Slicer Column
  • And your Outer CALCULATE to be your Table Name.

Conclusion

You can see that by counting your Slicer how dynamic you can make your data.

You can download the PBIX file with the example data here (Item count on Slicer sheet): UnPivot Other Columns.pbix

BI-NSIGHT – Power BI (Mobile App Update – Android Tablets, Navigation Preview, AMA, Driving Power BI Adoption in your Organisation) – Excel (Get & Transform Update)

Gilbert Quevauvilliers December 8, 2016 No Comments

It does appear that as we approach the festive season, the Microsoft team are still full steam ahead, which is great for myself and the Data Analytics community.

Power BI – Mobile App update for Android Tablets

As per their blog post, it is great to see that there now is an App for the Android tablets, whilst this has been around for quite some time on the iPad tables, it has now come to Android.

It is great to see and you can find the blog post here: Now in preview: the Power BI you love on your Android tablet

Power BI – Navigation Preview

I have to say that I have already enabled the preview features, and whilst it did take a little time to get used to, it is definitely a step in the right direction.

It makes it easier to navigate when you have a lot of reports, dashboards and workspaces. As well as less clicks to get to the options that you want to use.

You can find more details here: Announcing the Power BI Navigation Preview

Power BI – AMA (Ask Microsoft Anything)

Depending on when you read this, you might be a little late, but it is great concept where you can ask Microsoft anything relating to Power BI.

I have no doubt that there will be some tough questions posed, but also a great wealth of information into Power BI.

You can see the dates and times above.

You can find the blog post here: Announcing the Power BI Ask Microsoft Anything

Power BI – Driving Power BI Adoption in your Organisation

This is a great blog post in which Microsoft gives an example of how they have used and enabled the business into data driven decisions using Power BI and how to get it adopted within Microsoft.

The really interesting part I think is that Microsoft is a large multi-country company, so if they can adopt Power BI in such a large scale, then it should be achievable with any other companies.

And not only that, but because Power BI is their own product I often like to think that because they build it, they might not use it as much as an external organisation, but they are which is great to see.

I would recommend reading through this if you are looking to get your organisation into Power BI: Driving Power BI adoption in your organization – Learn how Microsoft does this at scale

Excel – Get & Transform Update

It is great to see that there have been so many ypdates to the Get & Transform for Excel. I often feel like the Excel people do get left behind due to the sheer pace from the Power BI Team.

All of the updates are shown below

  • Web Connector—UX support for specifying HTTP Request Headers.
  • OData Connector—support for “Select Related Tables” option.
  • Oracle Connector—improved Navigator preview performance.
  • SAP HANA Connector—enhancements to parameter input UX.
  • Query Dependencies view from Query Editor.
  • Query Editor ribbon support for scalar values.
  • Add custom column based on function invocation.
  • Expand & Aggregate columns provide support for “Load More” values.
  • Convert table column to a list—new transformation.
  • Select as you type in drop-down menus.

You can read their blog post to get more details: November 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Using Parameters in Calculated Measures (Making Calculated Measures Dynamic based on Parameter)

Gilbert Quevauvilliers December 7, 2016 2 Comments

What this is going to explain below is how I use a parameter that a user has entered to make a Dynamic Calculated Measure based on the Parameter entered.

Example Scenario

  • In my model I have got Parameters for the Fiscal Start Year
  • So I want to create a calculated Measure that will get me the values for 4th Year going forward
    • So what we are looking to calculate is the following, which is the Value for 2015-16
  • And then if we change the Parameters for the Fiscal Start Year as shown below
  • So I want to create a Calculated Measure that will get me the values for 4th Year going forward
    • So what we are looking to calculate is the following, which is the Value for 2017-18
  • But using the same calculation

Getting the Parameter into a table

In the steps below I am going to explain how to get our Parameter Value for the Fiscal Start Month into a table in the Query Editor

  • Go into the Query Editor
  • Then click on New Source, and select Blank Query
  • Now in my Example I name the Query “Parameter – Fiscal Start Year”
  • Next I clicked on Advanced Editor
  • I then put in the following M Code

    let

    Source = #table({“Fiscal Start Year Number”},{{#”Fiscal Start Year”}})

    in

    Source

    • As you can see above I created a table with the Column Name “Fiscal Start Year Number” highlighted in PURPLE
    • And then defined the column to have the Parameter called #”Fiscal Start Year”
      highlighted in LIGHT BLUE
  • This then results in the following table as shown below.
  • What I then did was to use the standard Query Editor Functions and changed it to the Data Type of Whole Number
  • NOTE: This is so that I can then use it to go backwards and forwards for my dynamic calculated measure.
  • The final step was to load it into my Power BI Desktop Model

Creating the Dynamic Calculated Measure

Next I will explain how I created my dynamic calculated measure, which will change based on what the Fiscal Start Date Parameter that has been selected.

  • Below is my Dynamic Calculated Measure syntax with the explanation what I did afterwards.

    Total Value for 3rd Year =

    CALCULATE (

    [Total Value],

    FILTER (

    ‘Budget Data without New Year’,

    ‘Budget Data without New Year'[Fiscal Year Number]

    = VALUES ( ‘Parameter – Fiscal Start Year'[Fiscal Start Year Number] ) + 3

    )

    )

    • What I have done is to name and start my Calculated Measure and put in the filter.
    • The part where the magic happens in the Filter
      • In here is where I specify that I want to look at the ‘Budget Data without New Year'[Fiscal Year Number] and compare that with the VALUES from my parameter ‘Parameter – Fiscal Start Year'[Fiscal Start Year Number]
      • And then I always want to go forward by 3 to get the 4th year going forward
  • So once this has run I should get the following as shown previously for Fiscal Start Year Parameter 2012
    • And I get the following below.
  • So if I change the Fiscal Start Year Parameter to 2014 once this has run I should get the following as shown previously.
    • And I get the following below.

Conclusion

By using Parameters and putting it into a table means that this can then be used to dynamically create other calculated measures.

You can download the PBIX file with the example data here: UnPivot Other Columns.pbix

BI-NIGHT – Power BI (Export to PowerPoint, New Visual Liquid Fill Gauge, Tech Tip Display Text, Automating Power BI Desktop Refresh, Webinar Create PowerApps to send real time data to Power BI) – DAX Studio (Release 2.5.0)

Gilbert Quevauvilliers October 20, 2016 No Comments

Another week closer to SQL Pass, and here are the recent updates and interesting articles

Power BI – Export to PowerPoint

It is great to see that after some screenshots from Microsoft Ignite that this has already been implemented into the Power BI Service. I am sure that there will be more details around this in due course from the Microsoft Power BI team.

I did export a Power BI report to PowerPoint and it exported each report to a different slide. And when you clicked on the slide it would then hyperlink you to the report within the Power BI Service. I am sure that this is the first iteration, and that the features will evolve over time.

You can find the blog post from Microsoft here: Export Power BI report to PowerPoint (Preview)

Power BI – New Visual Liquid Fill Gauge

As you can see from above there is a new custom visual, the Liquid Fill Gauge. And this is another really handy visual when you quickly want to show how your value is doing.

You can find the blog details here: Visual Awesomeness Unlocked: Liquid Fill Gauge

Power BI – Tech Tip to Display Text in Tooltips

From the people at Power BI, they have released a tech tip Thursday, with this week’s tip being how to display text as a tool tip as shown above. This can be really handy for when people are interacting and using the data.

You can find the tech tip details here: Tech Tip Thursday: Displaying text columns in tool tips

Power BI – Automating Power BI Desktop Refresh

There was an interesting blog post by Teo Lachev, in which he shows 2 different ways to refresh your Power BI Desktop File automatically.

The one option is to use a PowerShell script developed by Michal Dúbravčík (I am fairly certain that it works, but as always check the code when downloading from the internet), or the other option is to use Power Update to do this for you. Which is really easy and simple to use, as well as being extremely cost effective.

You can find the details here: Automating Power BI Desktop Refresh

Power BI – Create PowerApps to send real time Power BI

Hopefully people will see this in time, and if not I am fairly certain that they will publish the webinar later.

But please if you are interested and are using Power Apps, this looks like a great webinar to see how you can leverage Power Apps to send real time data to Power BI.

You can find details here: Webinar 10/20: Create PowerApps to send real time Power BI data

DAX Studio – Release 2.5.0

I have to say that anyone that is developing using Power BI, Power Pivot or SSAS Tabular then the DAX studio is most certainly a must have tool to have in your tool belt.

It offers a whole range of really useful and helpful ways to understand exactly what you are trying to achieve. And ensure that you do it in the most efficient way possible.

You can find the updates to DAX Studio here: DAX Studio 2.5.0 Release

Power BI – Dynamic Row Level Security – Things you need to know to get it working!

Gilbert Quevauvilliers October 3, 2016 No Comments

Below I am going to explain some of the caveats that you need to be aware of when implementing Dynamic Row Level Security (RLS) in Power BI. Without this I
could not get Dynamic RLS security working for me and my data.

I found that there are a few things that are currently not mentioned anywhere and it took me some time to gain an understanding. So I am hoping that with this blog post it will make it easier for you to implement Row Level Security using Dynamic Security.

Below is the link to a blog post by Kasper De Jonge in which he explains how to very quickly get up and running with RLS, as well as providing a sample Power BI Desktop Model.

Power BI Desktop Dynamic security cheat sheet

And this is what the Relationship Model looks like, which will make the explanation below a little easier to understand.

Things you need to Know!

Below are the things that I learnt and that will help with understanding the bits that make it all work together.

Testing RLS in Power BI Desktop

The first thing that you will need to update if you have downloaded Kasper De Jonge’s Power BI Desktop model is for in the relationships area. This is because it is missing a key tick box, which without it the Dynamic RLS will not work.

  • Go into the (Relationships) area in the Power BI Desktop model.
  • Then edit the relationship between the UserGroup and Group Table, when opening you will see the following below.
  • Now in order for this to work, you will need to apply the tick next to “Apply security filter in both directions”
    • NOTE: If this is not enabled or ticked the Dynamic RLS will not work correctly.
  • So once done it will now look like the following below:

The next thing to note is if you want to test RLS in Power BI Desktop you have to ensure that you have included yourself in both the Users and UserGroup
Table.

If not, you will get the following screen when click on the button.

As you can see above the Bar Visual is Blank and the Sales Amount is Blank. This is because with my current login context I am not specified in any of the Dynamic RLS tables.

Which lead me onto the next piece in understanding how the Power BI Service works.

Testing RLS in the Power BI Service

What happened was when I was initially testing this, I put in myself as a user and then a fellow
worker as a user in both the Users and UsersGroup table.

I then uploaded the Power BI Desktop file to the Service. Once it was uploaded I went into the Security for my dataset and put in name under the Roles.

Now what I expected to happen is that when I went into the report I should only see the data for Group B and Group C, the reason is because in the UserGroup table I had rows for Group B and Group C

But when I went and viewed the Report I saw the following below. As you can see I can see all the data and NOT Group B and Group C.

This took me quite a to understand and I did try a whole host of things to get it working.

This is unconfirmed by Microsoft but my own conclusion was that because I am the person who is uploading the Power BI Desktop model into the Power BI Service, I must by default have Admin (Server Administrator) rights to the model.

So no matter what I do, I will always see everything. Which makes perfect sense because I am the author of the model.

So to test this I then shared my Dashboard with another user who only had access to Group C, and when he viewed the dashboard as well as the reports he saw the following below. (NOTE: I did add his email address under Security in the Dataset)

Conclusion

Whilst it is great to now have Dynamic RLS in the Power BI Service I did struggle for some time to get it working, as well as to understand how it all pieces and works together.

And since I now know the above information I have been able to successfully roll out and test other Power BI Models successfully.

Power BI – (Twitter Solution Template, Real-time Data, Embedded GA Pricing Update, On-Premise Data Gateway Update, Monitor On-Premise Gateway) – SQL Server 2016 (SSDT Update, SSMS Update)

Gilbert Quevauvilliers August 18, 2016 No Comments

Here is this week’s BI updates

Power BI – Twitter Solution Template

This is the second solution template offered by the Power BI team and this time I think they have done a fantastic job. As I know of many companies who are active on Twitter and currently either do not have the resources or the time to be able to complete not only analysis of your Twitter tweets, but also the sentiment analysis.

With this solution template it allows you to very quickly and easily get this up and running. Yes you will need to have an active Azure Subscription, as well as Power BI Pro, but I think that for the larger or even smaller organizations they have already got this set up.

You can find the blog post detailing more information and how to get it up and running here: Announcing the brand & campaign management solution template for Twitter

Below is the current pricing model for the Azure related instances:

And here is the link: Pricing Information for the Twitter Template

Power BI – Real-time data

The guys from Microsoft Power BI have released another blog post where they explain how to get started with streaming data into Power BI on a dashboard.

You can then also follow this blog post by Reza Rad where he explains how to set it up and test to see how it works (Monitor Real-time Data with Power BI Dashboards)

Which you can then use to try and implement the streaming data.

Here is the blog post: Real-time in no time with Power BI

Power BI – Embedded GA Pricing Update

The Microsoft Power BI team has released an update and simplified way to understand how the Embedded pricing works.

As you can see from the above it is very simple. As well as in the blog post they also detail exactly what makes up a report session. And it is very clear to understand and see how potentially there will be a cost associated to this.

You can find the additional details here: Power BI Embedded GA pricing update

Power BI – On-Premise Data Gateway Update

Another monthly update from the Power BI team for the On-Premise data gateway. And in this months release is the ability to refresh ODBC connections.

This is something that was requested and not only that, with ODBC connections your options are almost limitless in terms of what you can connect to.

You can find the blog post details here: On-Premises Data Gateway August update

Power BI – Monitor On-Premise Gateway

This is a great blog post by Brett Powell (InsightQuest) on how to monitor the On-Premise Gateway in terms of what is being used, and what connections are being made when.

This also means that you can combine this with the other performance counter values to see how this could potentially affect the performance of your server when the data refresh is happening.

You can find his white paper here: MONITOR ON-PREMISES DATA GATEWAYS

SQL Server 2016 – SSDT Update

It is great to see that they are also updating SSDT with regular updates.

In this release there are updates to the Analysis Services Tabular Model Explorer, which gives you a better view of the objects in your tabular model.

There are also updates for the Always Encrypted.

As well as fixes and improvements.

You can find the blog post here: Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

SQL Server 2016 – SSMS Update

Here is the monthly update for SQL Server Management studio.

There are a whole host of fixes and additions in the changelog.

You can find the details here: Download SQL Server Management Studio (SSMS)