Customizing Data Alerts in Power BI using PowerApps

January 16, 2018

So this happened yesterday – one of my customers pinged me and asked whether it is possible to set customized data alerts for her end users? I froze for a second, knowing that such a functionality is not available out of the box but knowing how flexible Power BI is, I decided to explore her use case further. Worst case, I know I have the backing of the world’s best product team, and could submit a request to build this for us. Basically, she wanted her end users to get data alerts if specific products got sold in the last 24 hours (which should have been easy with the regular data alerts functionality in Power BI), but the challenge was that she wanted her users to set (add/delete) their own products. As I said earlier, this functionality is not available out of the box but with the PowerApps custom visual for Power BI and some DAX, we can definitely create a workaround.

 

image

 

As with any of my PowerApps post, this is going to be long, so scroll to the bottom if you just want to see the end result.

 

Source

The data source that we will be using for this post is a simple excel file stored in One Drive for Business. The excel table gives the sales by company and date. The requirement is that the end users should get data alerts if any sales occurred in the previous day for any of the companies that they chose.


image

 

Solution Summary

To achieve the end result, we will create a table that maps the users to the companies, and then create a DAX measure in Power BI that shows the Sales if the conditions are met. The mapping of the companies to the users can be entered directly by the end users using a PowerApps custom visual within Power BI. The DAX measure can then be pinned to a dashboard and the alerts can be set on top of it. Also, Row Level Security has to be applied on the mapping table to ensure that the End user sees only his selections.

 

Solution

Follow the steps below:-

1) Create a user mapping table in an Excel file and store it in One Drive for Business (I am using Excel file stored in One Drive, but this could be any of the data source that PowerApps can read/write from and Power BI can read from). For now, I am adding 2 users – Admin and AdeleV and their selected companies for which they would like the data alerts.

 

image

 

The model would look like below. Note that the UserMapping table is a disconnected table.

 

image

 

2) Create a measure that satisfies the 2 requirements – filter selected companies for the user and sales from yesterday.

SalesAlert =
CALCULATE (
    SUM ( Sales[Sales] ),
    — Filter the Company table based on the companies mapped to the User in the UserMapping table
    TREATAS ( VALUES ( UserMapping[Company] ), Company[Company] ),
     — Filter sales for yesterday
    Sales[Date]
= ( TODAY ()1 )
) + 0

 

3) Create a new role and set RLS for the UserMapping table. The intention is to filter the UserMapping table to the current user who has logged in and that can be done with the expression below

UserMapping[UserID] = USERPRINCIPALNAME ()

image

 

4) Create a simple report with the SalesAlert measure that we created in Step 2. Note that I am also adding the User name, UserMapping table as well as the Sales table so that you can see what is happening when we are logging in as different users. Right now, I am still logged in as Admin.

 

image

 

Pin the SalesAlert card to the dashboard (along with the Username card, so that you can see who is logging in). I also created a simple data alert that sends an email if the threshold is above 0. The dashboard is shown below

image

 

5) Go back to the Power BI report, edit it in the Power BI Service so that you can create a PowerApps custom visual. For this demo, I am passing the UserID and Company in Power BI.

 

image

 

6) Then, I created an app with 3 screens and the 2 data sources used are the UserMapping table in Excel as well as the Sales table in Excel. Let us look at the Home screen first, where we will display all the companies that are selected for the appropriate user.

 

HomeScreen

 

The main highlights here are

  • A plus icon to navigate to the AddScreen – OnSelect property set to Navigate(AddScreen, Fade)
  • A dropdown to display the UserIDs from Power BI.
    • Items property set to Distinct(PowerBIIntegration.Data, UserID)
    • Default property set to First(PowerBIIntegration.Data).UserID
  • A gallery to display the companies for the selected UserID
    • Items property set to Filter(Mapping, UserID in Dropdown1.Selected.Value)
    • NextArrow Icon to navigate to EditScreen – OnSelect property set to Navigate(EditScreen, Fade)

 

7) The next Screen is the EditScreen. This screen is used to delete an existing mapping.

 

EditScreen

 

The main highlights here are

  • A X icon to cancel out from the screen – OnSelect property set to Navigate(HomeScreen, Fade); ResetForm(Form1)
  • A trash can icon to delete the selected record – OnSelect property set to Remove(Mapping, CompanyGallery.Selected); Navigate(HomeScreen, Fade)
  • An Edit form
    • Data property set to Mapping
    • Fields are Company and UserID
    • Item property set to CompanyGallery.Selected

 

8) The last screen is the AddScreen and is used to add any new mappings for the end user.

 

AddScreen

 

The highlights from this screen are

  • Edit form to add the records
    • DataSource property set to Mapping and Item property set to Defaults(Mapping)
    • Company and UserID fields are selected
    • Company – Items property set to Distinct(Table1, Company) and Default property set to First(Table1).Company. This will display all the companies from the Sales table in the dropdown
    • UserID – Default property set to User().Email
  • X mark to cancel out from the screen – OnSelect property set to Navigate(HomeScreen, Fade); ResetForm(EditForm3)
  • Tick mark to add the entry – OnSelect property set to SubmitForm(EditForm3); Navigate(HomeScreen, Fade); ResetForm(EditForm3)

 

9) Once you save and publish the app, also make sure that you are sharing it with all of the end users. 

10) Save the Power BI report, and publish it as an app. Share the App with the end users (in my case, Adele and Alex).

11) Make sure that the end users can access the App and also, make sure that each one of them creates a data alert on the dashboard with the same condition (just like we did in Step 4). The image below shows the data alert for Adele.

 

image

 

12) Now the users should start getting alerts for the companies that they have selected. Based on our data alerts,

  • Admin should get a data alert email if JPM, XOM or NVDA has a transaction for yesterday. Note that if you are an administrator, you will get the alert even if the data alerts for one of the end users’ condition is true. This is because the way we have setup the measure and Row Level Security, an administrator can see all the companies for all the users, and hence will get an alert if one of the selected companies has sales for yesterday (Jan 15).

image

 

  • Adele should get a data alert email only if there is a transaction for NVDA (44 for Jan 15). In this case, she will get a data alert.

image

 

  • Alex will not get a data alert as he has not selected any company.

 

13) Now login as Alex, and navigate to the report. Use the PowerApps custom visual that we created before to enter a new selection (in this case, JPM) for Alex. You can either wait for the dataset to be refreshed, or in my case, login as an admin and refresh the dataset manually. Once the dataset is refreshed, Alex can see the company that he selected in the mapping table, and can also see that the SalesAlert measure has been updated. 

 

AddAlex

14) At this point, Alex should also receive an email alert.


Summary

Even though customized data alerts are not possible out of the box in Power BI (so far, as of 1/16/2018), it is possible to use the PowerApps custom visual, and make customized data alerts for each of the end users as well as give them the ability to modify / add their selections for data alerts.


Related

Adding Comments & Sending Emails in Power BI using PowerApps

 

 

Posted by SQLJason in Power BI, PowerApps, 0 comments

Adding Comments & Sending Emails in Power BI using PowerApps

January 9, 2018

A couple of days ago, a new custom visual was released for Power BI – PowerApps (in preview mode). As mentioned in the team blog, there are a ton of use cases–

you will be able to pass context aware data to a PowerApps app which updates in real time as you make changes to your report. Now, your users can derive business insights and take actions from right within their Power BI reports and dashboards. No need to switch tabs to open the separate apps, copy paste data from one window to another or worry about fat fingering the wrong customer id or invoice amount.

If you think about it, this is a game changer – you finally have a BI tool that allows you to collaborate and take actions right within the report. How many times have you looked at a report, found out an insight and wished that you could send an email to the account manager, only to forget later? Well, now you don’t have to worry about that, as I am going to show you an example of how we can collaborate by adding comments within the report (not just comments, but context aware comments, based on what you are selecting) as well as show how to send emails (to the appropriate people based on your selection).

 

Note: This is a long post, as PowerApps might be new to many BI folks and I wanted to include as many images as possible. If you just want to see the end result, please scroll to the end.

 

Adding comments and sending emails in Power BI using PowerApps

 

Steps

1) For this demo, I am using a simple Power BI report that shows the stock volume for three companies by date and their account managers. I made a simple line chart along with a slicer for the companies in Power BI Desktop, as shown below.

 

image

 

2) Deploy the report to Power BI service, and then edit the report. Add the PowerApps custom visual from the store.

 

AddPowerApps

 

3) Now click on the PowerApps custom visual, drag and drop the Account Manager and Stock into the PowerApps data field, and click on Create New. 

 

CreatePowerApps

 

4) The intention of the PowerApps visual is to provide an input area for the comment as well as to send emails to the account managers of the respective companies. For storing the comments, I am using a simple table in an Excel file and the format of the table is given below

 

image

 

Note that we have a column for the stock, comments, date of entry and the user who entered the comment. Now, go ahead and create 3 new data sources – Office365 Outlook, Office365Users and the Table in the excel file.

 

image

 

5) At this point, I started editing the comments page (which is the first page) and the final result is given below.

 

image

 

Some of the key changes that I did are:-

  • Changed the Data field in the Gallery to filter for only those fields in the Table1 (comments table in excel) which are filtered in Power BI.

 image

  • Changed the Gallery layout to display the Stock, User, Comments, Date from the Excel file as well as the photo of the user. You can get the photo of the user by using the formula given below

 Office365Users.UserPhoto(Office365Users.UserProfile(ThisItem.User).Id)

 

 image

  • Changed the background color of the Gallery to blue, and also added 3 buttons – Comments, Email and the plus icon. The Comments and Email button are given appropriate colors so that it looks like a tabbed structure. Also, their OnSelect properties are set to navigate to Email page and AddComments page on selecting.
    • Comments – No formula.
    • Email – OnSelect property is set to Navigate(Email, Fade)
    • Plus Icon – OnSelect property is set to Navigate(AddComment, Fade); ResetForm(EditForm1)

 

6)  The next page is the Email page and is used when you want to send an email to the account manager without adding a comment. For eg, maybe you want to send a confidential email to the account manager about an insight or question.

 

SendEmailTab

 

Some of the key changes that I did are:-

  • Created a form for Table1 and displayed the stock & comments. Also added the User and Date fields, but hid them (by setting the DisplayMode property to Parent.DisplayMode.Disabled) after giving a default values
    • User – Default property set to User().Email
    • Date – Default property set to Today()
  • Created a button for Send Email, which will send the message as an email. Set the OnSelect property to the formula given below

Office365.SendEmail(“jason143@gmail.com”, “Comments for ” & DataCardValue4_1.Selected.Value & ” from ” & User().FullName, DataCardValue2_1.Text); ResetForm(EditForm1_1); Navigate(Comments, Fade)

  • Added the Default value of the Stock to the formula below, so that it will default to the value selected in Power BI

LookUp(Table1,Stock=First(PowerBIIntegration.Data).Stock).Stock

 

7) The next page is the AddComment page. This is the page that you navigate to when you clicked on the Plus Icon in the Comments page and is used for adding comments.

 

AddComments

 

Some of the key changes that I did here are:-

  • Similar EditForm as the Email page – displaying only Stock and Comment and hiding User and Date
  • A toggle that determines whether you want to send an email along with adding the comment or just add the comment only.
  • 2 icons on the top
    • one for canceling the form – OnSelect property set to Navigate(Comments, Fade); ResetForm(EditForm1)
    • one for submitting the form. OnSelect property is set to

If(Toggle1.Value=true,Office365.SendEmail(“jason143@gmail.com”, “Comments for ” & DataCardValue4.Selected.Value & ” from ” & User().FullName, DataCardValue2.Text)); SubmitForm(EditForm1); Navigate(Comments, Fade)

 

8) Now you can save and publish the app. See the results in the Power BI page. I also added a toggle using bookmarks in the Power BI page to show/hide the comments / PowerApp visual.

  • Show contextual comments

 

PBI Show contextual comments

 

  • Add comments based on what you have selected in Power BI. Also note that an email has been sent to the appropriate account manager with the comment.

PBI Add comments

 

  • Send an email to the account manager directly (without posting the comments)

PBI Send Email

 

I am pretty sure you guys are going to find a lot more use for this custom visual. Note that this is currently in Preview, so it is likely that you will hit upon some bugs / issues, but those should be gone when it is released.

 

 

Posted by SQLJason in Power BI, PowerApps, 5 comments

Embedding Images in Power BI using Base64

January 2, 2018

Hi readers, Happy New Year! It’s been a while since I blogged and a lot of changes have happened in the last one year, including me moving to Microsoft as a BI Technology Solution Professional in the Healthcare Vertical. As part of my new role, I get to spend a lot of time with customers educating them on Power BI and showing them how we can solve real world problems using Power BI. During these engagements, I also tend to get a lot of technical questions and one of the questions that came up recently was – How can I embed images for my data categories into Power BI without providing the image URLs?

image

As usual, my first question back to the customer was – why do you need this feature or what is the use case here? Some of the reasons he gave were:-

1) The image will be stored internally in the pbix file, so there would be no need to host the image somewhere else. Currently, they are downloading the images and hosting it on their SharePoint Online site.

2) Some of the public reports are using images that are hosted in public sites (like Wikipedia), and there is a chance that the image URLs might change (and hence stop displaying the image in the PBI report)

3) You can access the images offline (for eg, if you are working on Power BI Desktop and there is no internet connectivity).

 

And I completely understood his concerns as I had the same issue with some of the public facing reports that I made, for eg., the US Election report that I had made 1 year back. The images for the candidates were sourced from Wikipedia and certain candidates like George Bush, Donald Trump, etc. are not displayed, because the image URLs are no longer valid.

 

image

 

This is where you can use my workaround to embed the images within the report by converting the images into Base64. Follow the steps below:-

1) Choose the image that you want and use any Image to Base64 converter to get the Base64 code. I used this website but you can use any.

Convert to base64

 

2) Once you get the Base64 code, prefix it by data:image/jpeg;base64, and not you can use it as an expression in any calculated column. In my case, I want to create a new column that will display the existing URL link if the candidate is not George Bush or Donald Trump, and use this new base64 code for George Bush & Donald Trump.

Img = SWITCH([Nominee],
“George Bush”, “data:image/jpeg;base64, base64code“,
“Donald Trump”, “data:image/jpeg;base64, base64code
,[ImgUrl]
)

Note that I am not writing the actual base64 code in the formula above, as it is very long. You can substitute the base64 code in the yellow highlighted area.

image

3) Make sure that the category of the column is set to Image URL. Even if we are embedding the image, the data category has to be set as Image URL. This also enables us to mix and match as in my scenario, where some of the entries are coming in as actual URLs and some as base 64 code.

image

4) Now you can use this column in your report for displaying images. In my case, I am using the chiclet slicer to display the images. In the gif below, you can see how the original column (ImgUrl) is not working for George Bush and Donald Trump, but when I replace it with the new calculated column (Img) with the base64 code, it shows the appropriate images.

Displaying image as base 64

 

That said, there are some things that I have noticed while working with base64 code:-

1) Large images usually do not get displayed even if you paste the right base64 code. So make sure you are using small images with this method.

2) Since the base64 code can be really long, the code might soon get long & unwieldy if you are using it for multiple images.

3) Again, due to the length, you might not be able to add it to excel files (excel cells have a limit of 32,767 characters and the base64 code could easily be longer than that). Your best bet would be to add it as a calculated column most of the times.

 

Let me know in the comments if you notice anything else or have any more input on the same.

Update (01/05/2018)

Gerhard Brueckl (blog | twitter) has already taken this a step further and talks about automating the process by doing everything withing Power BI. Don’t forget to read his blog on this too – Storing Images in a PowerBI/Analysis Services Data Models

Posted by SQLJason in Power BI, 7 comments
Visualizing Merge Join Types in Power BI

Visualizing Merge Join Types in Power BI

July 27, 2017

Over the last couple of years, I have been actively involved in doing Power BI trainings for my clients. Because of that, I am always looking for new and easier ways to explain Power BI concepts to my attendees. Yesterday, I saw a blog post from Reza Rad on Merge Types in Power BI and realized that this is one concept I always have to explain by drawing on a whiteboard during my training sessions. That is when I started thinking – maybe I could create a Power BI report to explain the join types while merging queries. Being able to click through the different join types and seeing the results would definitely make it more easier to understand than me drawing or just talking talking about it. Also, to make it more useful, I wanted to keep the ability to add / modify / delete records in the two tables so that my attendees could see in real time how it will affect the resultant merged tables.

For demonstrating the solution, I made use of 2 simple tables: Table A consists of Customer ID and Customer Name, Table B consists of Customer ID and Email. We will be merging both of these tables using the Customer ID column. In the report, you can see Table A is on the left side, Table B is on the right side and the resultant merged table is on the bottom. We also have a slicer on the top to choose the Join Type, and just under it, we have a description for the Join Type as well as a Venn Diagram. For both Table A and Table B, we have a IsJoined column which denotes whether the corresponding row is present in the Merged table for the selected Join Type. I have embedded the report below, feel free to click and see for yourself (or click on this link to see the full page view).

* The initial version of the report had only the 6 join types available out of the box in Power BI. After sharing this version of the report on Twitter, Imke Feldmann (t | b) said that it would be nice to display the Full Anti Join also, which is not available by default but can be easily added with the help of simple M code (something along the lines of Table.Combine({LeftAntiJoin, RightAntiJoin})). So I added that to the latest version of the report, along with a message that it requires custom code when the Full Anti Join option is selected.‏

 

Now, if you are reading this, most probably you are interested in learning how this was done. To be honest, this report ended up being a little more tricky than I thought it would be and it has some hidden tips and tricks.

– Table A and Table B data is entered through Power BI. So you can add more records and see how the merged table would look like for the changed data. For eg, what happens when I have duplicate customer ids and how will it affect my merged table?

– How does the slicer have images under it? – Chiclet slicer

– How did I make the Venn Diagram to change based on the filter selection? – Use Synoptic Designer to create the Venn diagram, and then link it to your dataset using a DAX measure that will highlight the appropriate area.

– How does the IsJoined column show whether the source table’s record is present in the merged table? – DAX Measure

– How do I display the right results in the Merged table? – A combination of using Power Query and DAX measures.

I know I have answered the questions only on a high level, but if there is enough interest in knowing more about this, please let me know and I might end up writing a follow up for this post detailing all the techniques. Let me know if you have any further questions apart from what I have listed or feedback / bug reports on the same, and I will try to accommodate / fix it as much as I can. Meanwhile, feel free to download the report and play with it yourself. Also, you know where to point the next time someone asks you on the different join types in Merge Queries within Power BI.

I have also published this report to the Power BI Community Data Stories Gallery. Feel free to comment / like / simply interact with the other reports and users out there.

Update

The report has been updated with a second page explaining the join types using Join Diagrams. The join diagrams are inspired from this post – https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/ and was pointed out to me on twitter by @thesqlgrrrl.

Posted by SQLJason in Power BI, 15 comments
Visio Custom Visual (Preview) for Power BI – Quick Look

Visio Custom Visual (Preview) for Power BI – Quick Look

June 22, 2017

A week back, I was at the Data Insights Summit, where I got to hear in person many exciting updates for Power BI. One of the updates was the release of a preview version of a new custom visual – Visio for Power BI. At that time itself, I registered myself to try out the new custom visual but it took almost another week for the team to send me the download files for the Visio custom visual (pretty sure they were flooded with preview requests from excited users like me). That said, I have been trying out the visual for the last 2 days and decided to write down a quick review of the preview version.

How to get the Visio Custom Visual for Power BI

You can request the private preview for the Power VI Visio custom visual by clicking on this link – aka.ms/visio-new and filling in the form.

How to use the Custom Visual in Power BI

1) For the purpose of this report, I created a simple excel file (OrgData.xlsx) containing Name, Title, Reports To and Salary.

sample data

I also added some pictures of the employees in a folder.

employee headshots for org chart

2) I imported this data into Visio to create a Org Chart (follow the steps from this link).

Org chart in Visio

3)  Save the Visio diagram to One Drive for Business or SharePoint Online where your team also has access.

saved visio file in One Drive for Business

Click on the Visio diagram and then copy the link into a text file for future use.

4) Now open Power BI desktop and import the Excel file with the org data. After that, import the Visio custom visual and select it on the reporting canvas. Add Name to the ID field, and then you should see a dialog box to input the Visio diagram’s URL that we copied in the previous step. Click on connect after that, and also add the Salary in the Values field, so that we can see the Org Chart display the colors. Check out the gif below for more details.

Visio Custom visual in Power BI

5) Notice that the visio diagram is not coming up in Power BI Desktop. This is a limitation of the current preview version, and the diagram will only be visible when you view it in Power BI Web. Add a simple table with Name and Salary next to the Visio custom visual and then publish the report. Now you should see the Visio diagram in the report.

Visio custom visual in Power BI Web 

Note that you can click on the org chart and see the table getting filtered for the selection. However, it is not possible to make multiple selections using CTRL+Click in the Visio diagram, as we can do in the other native charts.

My Thoughts – The Good & The Bad

1) This visual provides a great way to make some cool visuals easily. Apart from the Org charts, I also experimented with Flow charts, network diagrams, floor plans and it was great to see how easy it was to make those charts in Visio and integrate them within Power BI.

2) This is more of a Visio feedback rather than for the Visio custom visual for Power BI. You can use Visio to make some charts that are not available natively in Power BI like Org Charts, Flow charts, etc. from Excel data (or other sources) automatically. So if something changes, it is easy to create a new one by importing the data again and then saving it in the same location in One Drive for Business / SharePoint Online. The Power BI report seems to pick up the latest version of the Visio diagram every time the browser is refreshed (even though the official documentation says that you might need to re-insert the custom visual sometimes).

However, it would have been better if the shapes were automatically added or deleted in Visio based on changes in data, rather than manually adding them or recreating them. Even though this feature is not present in most charts, I did notice that there are some like the “Cross Functional Flowchart using Data Visualizer” in Visio where the shapes get added/deleted by just clicking the Refresh button in Visio.

3) I am pretty sure this is just a limitation of the Preview version – the visual gets displayed only on Power BI Web version and not in the desktop.

4) Currently, it looks like you can’t do multiple select (using CTRL+Click) on the shapes with the Visio custom visual. It would have been nice if we could do that just like we do in all the other native visuals in Power BI.

5) The usefulness of this visual can be greatly enhanced if there was a way to automate the refresh of Visio diagrams based on the change of data, saving the changed Visio diagram to One Drive for Business/SharePoint Online and then seeing the latest version without any issues in Power BI. I am still investigating if there is a way for it.

Apart from what I have mentioned, the official documentation also mentions the following things about the Preview

1. Visio custom visual needs to access the Visio diagram so in cases where Power BI user’s sign-in information can’t be accessed via Single Sign-on, the user might be presented with a sign in prompt and they need to sign-in to authenticate themselves.

2. If clicking on sign in button doesn’t do anything then it could be due to a known IE/Edge browser behavior when Power BI and SharePoint are in different security zones, please add both the Power BI domain and the SharePoint domain to the same security zone and try again.

3. Data graphics applied to Visio diagram from Visio client are removed.

4. In case your diagram has complex styles, themes, fill patterns etc., you might notice some visual differences between the Visio diagram in the Visio client and the diagram rendered in the Visio custom visual.

5. Large diagrams with shape count over 2000 are not supported.

6. In case you need to add new shapes that map to your Power BI Data, or remove shapes that have been previously mapped please verify the report. In case you observe any issues, you might need to re-insert the Visio custom visual and map the shapes again.

It is pretty exciting to see all these features in the Preview version of this custom visual, and I can’t wait to see what else is going to be available once this is no longer in Preview. Also, the general trend of trying to integrate different products like Visio and Power Apps into Power BI is extremely heartening.

Posted by SQLJason, 4 comments
Dynamic Grouping in Power BI using DAX

Dynamic Grouping in Power BI using DAX

March 1, 2017

It has been quite a while since I posted something and was already thinking of dusting up my tools. That was when I was going through the Power BI Community forums, and found an interesting question –

Requirement: The user wants a report with a column chart. The X axis will have Subcategory Name and the value will be the sum of Internet Sales. Along with this chart, the user will have a slicer where they can select the Subcategory Names. The column chart should “update” showing one column for each selected subcategory, and another column named “Others” with the summed amount of the rest of the unselected categories.

Basically, they wanted a dynamic group called “Others” and the members within this group should change based on what is selected on the slicer.

This would be a good time to show a visual representation of what the requirement means.

1 Requirements

You can see that there is one individual (green) column for every selected Subcategory and also one (orange/red) column called “Other” which has the summed up value for the rest of the unselected categories.

For solving this, follow the steps below:-

1) The “Other” member is not available in any existing column. So we will have to create a new table having a column for all the subcategories, as well as an additional member for Others. For this, I made a new calculated table in Power BI using the formula below

ProdSubCat_List =
UNION (
    — get the existing values of subcategory name   
    VALUES ( ProductSubcategory[Product Subcategory Name] ),
    — add the other member
    ROW ( “SubCategoryName”, “Other” )
)

The Subcategory column from this table has to be used in the charts, since this is the only column which has the “Other” member. At the same time, this table is a disconnected table (which means that there is no relationship between this table and the rest of the fact/dimension tables), so we will not get any proper values if we just use the Sales measure with this column in a column chart. For that, we will have to create a custom measure.

2) The next step is to make a measure which will display the values

NewSalesMeasure =
VAR SelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR UnSelectedSales =
    CALCULATE (
        [Sales Amount],
        EXCEPT (
            ALL ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProductSubcategory[Product Subcategory Name] )
        )
    )
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        ALL ( ‘ProductSubcategory'[Product Subcategory Name] )
    )
RETURN
    IF (
        HASONEVALUE ( ProdSubCat_List[Product Subcategory Name] ),
        SWITCH (
            VALUES ( ProdSubCat_List[Product Subcategory Name] ),
            “Other”, UnSelectedSales,
            SelectedSales
        ),
        AllSales
    )

 

Note that we are making use of 3 variables – SelectedSales, UnSelectedSales and AllSales to handle the 3 conditions that can arise.

SelectedSales will match the member values in the our calculated table (ProdSubCat_List) with the Subcategory names in the original Subcategory table and get their corresponding Sales Amount.

UnSelectedSales will get the Sales Amount for all the unselected Subcategory names, and we make use of the EXCEPT function for this.

AllSales is the total Sales Amount for all the Subcategories, and is used for showing the grand total.

3) Create a column chart with ProdSubCat_List[Product Subcategory Name] on axis and NewSalesMeasure on values. Put a slicer which has ProductSubcategory[Product Subcategory Name]. Now you can see the required end result.

2 End Result

Posted by SQLJason in DAX, Power BI, 10 comments
Hex Tile Grid Maps for Power BI

Hex Tile Grid Maps for Power BI

April 21, 2016

I have always been fascinated by maps as a child, and could spend endless hours looking at the globe my parents got me as a present for my 5th birthday. I was so hooked on to it that my parents even considered removing it from my room fearing that it could hamper my social development (and this was in spite of my  parents being extremely proud that I could tell most of the countries and their capitals around that time!). Even though maps don’t intrigue me to that level anymore, I still follow them as part of my job and have written quite a number of blogs on getting spatial information in the Microsoft stack, starting from SSRS 2008 R2. So it was kind of natural that when I saw a couple of hex grid maps floating around my twitter feed a couple of months ago, I thought of reproducing it in Power BI as I knew it could be done.

Hex tile grid maps for Power BI

First of all, let us start with an introduction of hex maps and why they could be useful. Regular choropleth map is a tried and tested visualization for area maps but it carries the risk of under-representing some areas. For e.g., in a regular choropleth map of the US, DC is hardly visible along with some other North-Eastern states. A hex tile map solves this issue by giving each state equal weight. However, it comes with it’s own set of problems like balancing between depicting unique geographical features (like Texas and Florida being the southern most part of the country) versus depicting bordering states accurately. Because of this reason, you will find more than one version of hex grid maps and it is perfectly ok to choose the one that suits your need more appropriately. Now you can follow the steps below to reproduce a hex tile grid map in Power BI (and don’t forget to check out the Power BI report that I made with this technique at the bottom of my post):-

1) Choose a version of the hex tile grid map that you like from the internet. Or you can even make one easily in PowerPoint or any other image processing software (as it is just a collection of hexagons) based on the image that you get from the internet and save it as an image.

Making hexagons in powerpoint

2) Go to http://synoptic.design/ and upload the image to the synoptic designer by dragging and dropping the image to the designer.

Uploading to Synoptic designer

3) Ensure that the second icon on the bottom left is enabled (which helps us to automatically discover new areas). Now you can just click on the hexagons and the synoptic designer automatically discovers the areas for you, which is super cool.

Using the automatic discovery of areas icon in Synoptic designer

Now, for most people, this should be more than enough and the results come out really good. In my case, I decided to take a step further as I was planning to share the file for the community. If you notice carefully, you can see that more than 6 vertices are being plotted by the designer automatically (check out the multiple vertices in the section I highlighted).

Multiple vertices being recorded

To avoid this, I just wrote a bunch of formulas which would calculate the vertices in plain old excel and then just copied the 6 pairs for each of the 51 states manually.

Replacing it with just 6 vertices

Make sure that you map the areas to the appropriate state name / code also.

4) After this, your map is ready and you can just export it to Power BI, which would save the map data as a SVG file in your computer.

Export as SVG file for Power BI use

I would also request that if you make some interesting maps / shapes, please consider submitting it to the gallery so that other community members can also reuse it. I have submitted my map to the gallery and hopefully it will be approved by the SQLBI team (who created and still supports this wonderful tool).

5) Now open Power BI, and download the Synoptic Panel from here (if you don’t already have it) and import to Power BI. Once you have done that, click on the Synoptic Panel to add it to Power BI, and add the state code (which is the filed we are going to bind our dataset with the map) and a measure (like Total Votes) to it. Then click on the “Select Map” icon.

Synoptic Panel in Power BI

Then browse to the SVG file we just downloaded from Synoptic Designer and you should have your basic version of the hex tile map ready. Feel free to experiment by adding measure values to the Saturation or State values.

Basic hex tile grid map in Power BI

Now as a reward for making it till here, I thought of letting you play with this simple report that I created using this hex map. In this report, you can select any year from 1916 and see the winning party of each states (I only included the data for Republican and Democratic parties), as well as the nominees of the election. You can also see the number of electoral votes they won along with the popular vote %, which gives some pretty insights. For e.g., it is interesting to see that George W Bush won the election even though he got fewer popular votes than Al Gore in 2000. Click on the Expand icon to see the report in full screen.

Posted by SQLJason in Power BI, Spatial / Map Reports, 8 comments
NBA style Shot Charts in Power BI

NBA style Shot Charts in Power BI

February 11, 2016

Recently, I created a NBA shot chart in Power BI as part of my entry to the Microsoft Power BI Best report contest and I had got a lot of questions on how I made the visual. So I decided to write a quick post on how I made the shot chart as well as use this opportunity to present my entry, which got selected as one of the Top 10 finalists in the contest.

Note

My interactive contest entry is embedded in this post (thanks to the new Publish to Web feature in Power BI) and a full screen version of the same report can be obtained from here.

My entry is an analysis of the first 35 games played by Stephen Curry from the Golden State Warriors in NBA. The main feature of the entry is a Shot chart which shows the position from which he attempted his shots and the color denotes whether he made or missed it.

1 Shot Chart

To make a similar shot chart, follow the steps below:-

1) The most important part of any report is getting the data. I had a few sources for my data (www.nbastats.comwww.nbasavant.comwww.datavizdoneright.com) from where I directly got my (X,Y) position data. But if you are really serious, you might want to look at the following blog posts which show how to scrape data directly –

How to create NBA Shot Charts in R

How to create NBA Shot charts in Python

2) Once you get your (X,Y) location data, you can import the results into Power BI and then create a scatter chart from the data.

2 Scatter chart

3) Get a background image for the basketball court (I used one that I found from  www.datavizdoneright.com since it had the logo of Golden State Warriors and looked nice). Now you can import the image and place it behind the scatter chart. Make sure that you send the image to the back, as you need the scatter chart on top so that you can interact with the dots by clicking.

3 Arrange

4) Now the hard part is resizing the scatter chart to the size of the court. I turned off the X and Y axis, and then turned on the X,Y reference lines so that I know where the center needs to be.

4 Resizing court

5) Once you have found the right fit, you can turn off the reference lines also and then add the shot result to differentiate between made and missed attempt. You can also add a chiclet slicer with the opponent images to see the shots by teams as shown below.

4 Final shot chart

That said, there is already a custom visual called the Enhanced Scatter Plot which allows you to put an image behind a scatter plot chart. I couldn’t make my data line up with the image, and hence I had to do it the hard way. You might find it easier to use the Enhanced scatter plot directly. Hope you liked my version of the shot chart!

Posted by SQLJason in Power BI, 11 comments
Custom Indicators in Power BI using Chiclet Slicers

Custom Indicators in Power BI using Chiclet Slicers

November 6, 2015

First of all, happy Friday! As we get ready to enjoy the weekend, I thought of noting down a quick tip on how to use the totally awesome Chiclet Slicer to display custom indicators in Power BI. If you are hearing about the Chiclet Slicer for the first time, please do check out the official Microsoft blog on this as it is a very useful viz. For people who follow my blogs, you would remember that I had already written down a technique to create Indicators in Power BI before. But the main drawback in that approach was that there was no way to color the indicators, and also we were limited by the set of Unicode characters that could be used as indicators. With the advent of the chiclet slicers, we can now dynamically display any image as our indicator and this post will precisely show you how to do it. Custom Indicators in Power BI using Chiclet slicers For this demo, let’s say – I want to display a green up arrow or a red down arrow based on whether my measure is positive or negative. For that, follow the steps below:-

1) Open the Power BI desktop file where you want to add the indicator, and then go the data tab. Click on the New Table button.

Calculated table in Power BI

2) It is important to understand that the chiclet slicer, just like the regular slicer, can only display table fields or calculated columns (and not measures). So we have to create a table with a list of all the “states” or possible values. In my case, we can have only 2 states – Up and Down. Use DAX to create a table with 2 rows – Up and Down. Also, add the image url for each of the state (in my case, an image url for the up and down arrows).

Indicator =
UNION (
    ROW ( “Indicator”“Up”,
    “ImgURL”“http://www.clipartbest.com/cliparts/nTX/EGB/nTXEGBLTB.png” ),
    ROW ( “Indicator”“Down”,
    “ImgURL”“http://www.clker.com/cliparts/D/8/S/c/z/3/red-down-arrow-md.png” )
)

DAX for calculated table in Power BI for Indicator states

Note that we are making use of the calculated table feature in Power BI to create a table with a list of states.

3) Let us say that I have a measure called Metric which shows either positive or negative value. Right now, I am just hardcoding it to -30.

Add metric

4) Now create a new measure which will display 1 for Up if the measure Metric is >=0 or display 1 for Down if the measure Metric is < 0

LinkedMeasure =
SUMX (
    VALUES ( Indicator ),
    IF (
        (
            [Metric] >= 0
                && VALUES ( Indicator[Indicator] ) = “Up”
        )
            || (
                [Metric] < 0
                    && VALUES ( Indicator[Indicator] ) = “Down”
            ),
        1
    )
)

Add measure to display Indicator

5) On the Report tab, add the Indicator column and the Linked Measure to the canvas, and then convert it into a chiclet slicer (make sure you download and import this custom visualization from the Power BI Visuals Gallery before this step). Also add the ImgURL field to the Image field. You can change the Image Split property under the Image section to 100 from the default 50, so that the Image occupies 100% of the space

1 Add Chiclet Slicer

6) Hide the borders and also turn off the headers, so that only the image is visible.

2 Hide Borders

7) Now you can add a textbox besides the chiclet slicer to display the metric. Now go ahead and change the values of the metric, and you can see the chiclet slicer automatically update itself with the right indicator.

3 Dynamic indicator

The chiclet slicer is pretty good on it’s own as a way to slice data, but the ability to display custom images takes it to the next level. You can use it for a lot of tips and tricks, and I hope this post gets you thinking on what all you can do with this. And there goes your weekend, BOOM!

Note

As usual, make sure you look at the date at which this post was published and the version of Power BI. Since Power BI has a rapid release cycle, I would expect some of the features to change. Hence, always check whether a new feature makes it more easier to implement your scenarios like this one. The version I used is given below.

image

Posted by SQLJason, 6 comments
Performance Problems with IF statement execution in SSAS Tabular

Performance Problems with IF statement execution in SSAS Tabular

November 4, 2015

Due to the high compression rates and stellar in-memory architecture of SSAS Tabular, most people with smaller models do not experience performance problems (in-spite of employing bad data modeling techniques and inefficient DAX measures).  However, as the size of your models increase, you will start to see performance issues creep up, especially if you are not paying attention to data modeling and DAX measures. Last week, I gave a presentation at the PASS Summit 2015 on my experience of building a 150 GB Tabular model in SSAS 2012. During that, I shared my story on how some of the DAX measures with IF statements were causing performance issues and how to work-around that issue by rewriting your DAX measures. During that day, Microsoft also announced that they resolved this issue in SQL 2016, so I thought of demonstrating the issue, workaround and also the fix in SSAS 2016.

Performance problems with IF statement in SSAS Yabular

Issue in SSAS 2014 (& older versions)

For demonstrating the issue, I will be writing queries against the Adventure Works model in SSAS 2014 and using MDX Studio to show the server timings. Let me start with the below query

WITH MEASURE ‘Date'[test] = If ( 1 = 2, [Internet Total Sales], [Reseller Total Sales] )
SELECT NON EMPTY { [MEASURES].[Test] } ON COLUMNS,
NON EMPTY (
{ [Date].[Calendar Year].Children },
{ [Product].[Product ID].Children },
{ Geography.[Country Region Name].Children } ) ON ROWS
FROM [Model]

The above MDX query defines a DAX measure called Test, which depending on the condition displays either Internet Total Sales or the Reseller Total Sales (To make it simple, I just made a static condition 1=2 but that can be replaced by any dynamic condition also). The query results should display the Test measure for Year, Product ID and Country. Now, normally we would expect that the Test measure should only execute the true part of the IF statement. But let us execute this in MDX Studio and see what actually happens.

  Storage Engine scans against SSAS 2014 (Original query)

You can see that both the branches of the IF statement are being executed, even though we expect only the true part to be executed. For smaller models, it might not make a difference but for large models with expensive measures, this might cause severe performance issues.

Workaround in SSAS 2014 (& older versions)

The workaround for this issue is to rewrite your DAX such that we ensure that the measures get executed only if the condition is true.

WITH MEASURE ‘Date'[test] = CALCULATE([Internet Total Sales], FILTER(Currency, 1=2)) + CALCULATE( [Reseller Total Sales], FILTER(Currency, 1<>2))
SELECT NON EMPTY{[MEASURES].[Test]} ON COLUMNS,
NON EMPTY({[Date].[Calendar Year].children}, {[Product].[Product ID].children},{Geography.[Country Region Name].children}) ON ROWS
FROM [Model]

Note that the measure has been rewritten as the sum of two CALCULATE functions. The key is to use a table in the filter clause within the CALCULATE that satisfies the below conditions

  • Is related to the fact table of the measure
  • Is low in cardinality (you can also use a low cardinality column instead of a table)
  • Is not being used in the calculations for the measure/condition. If yes, do some extra testing to make sure the performance is not worse

The reasoning behind the table being connected to fact table is because the calculate() with the false condition has to evaluate to zero / BLANK so that the result of the Test measure would only be the one appropriate measure. If the table is not related, you will end up with the sum of both the measures. A low cardinality table or column is preferred because in this technique, you will see that there are 2 additional queries being sent to the storage engine, which evaluates the FILTER part for the two measures. If the tables have high cardinality, the time for the FILTER queries will take more time. The reason why I said that the table or column should not be used in the measure calculations or condition is because I have seen that in certain conditions, this could actually make the performance worse and still execute both the branches. So just make sure you do some extra testing.

Storage Engine scans against SSAS 2014 (Workaround query)

That said, let us look at the scans for the above query. You can see that only the Reseller Sales measure is executed. Also, if you notice carefully, there are 2 extra scans which basically check the filter condition for Currency. In large models, these scans for low cardinality dimensions will be almost negligible and the time for these extra scans will be much lesser than the time taken to execute the other measure also. In this case, the Adventure Works model is just 18 MB, so you won’t see much of a difference.

New Optimization in SSAS 2016

SSAS 2016 CTP2.3 (and newer versions) has a new optimization for this issue – Strict evaluation of IF / SWITCH statements. A branch whose condition is false will no longer result in storage engine queries. Previously, branches were eagerly evaluated but results discarded later on. To prove this, let us execute the original query against SSAS 2016 and see the results.

Storage Engine scans against SSAS 2016

Now we can see only the relevant measure is being executed. Also, it is faster compared to SSAS 2014 versions of both the original query as well as the workaround. I hope this article will help people who are not on SSAS 2016 to optimize IF statements, and also help understand what the new optimization in SSAS 2016 – Strict evaluation of IF / SWITCH statements actually means. There are also a bunch of new features and optimizations in SSAS 2016 and you should check them out!

Posted by SQLJason, 2 comments