Highlighting Scatter Charts in Power BI using DAX

March 16, 2018

Another Friday, another tip. Power BI has undergone a lot of innovative updates over the years, but my favorite one has been a combination of the cross-filtering & highlighting  capabilities, multi-selecting across visuals and drilling down filtering other visuals. I feel having those capabilities are so important to make the flow of data exploration and analysis intuitive for end users, and I had written a long post back in 2015 where I compared Power BI to Tableau, the undisputed leader in data exploration at that time. Fast forward another 2-2.5 years, and there has been so much progress that Power BI is the new leader and other tools like Tableau & Qlik are trying to catch-up (in fact, we are at this junction where I could write a similar post on how it is easier to do data analysis in Power BI compared to Tableau – try checking out the number of clicks it would take to implement drill-downs, highlighting/filtering, etc. compared to Power BI or just the very basic task of putting 6 charts in one page for analysis). That said, there is one feature from my previous blog that was not implemented in Power BI – highlighting scatter/bubble charts. In Power BI, the scatter charts are not considered as area charts and hence you can only filter them and not highlight. This feature is useful when you have a lot of data points in your scatter chart and you want to see where a particular data point is with respect to the other data points. That said, you can make use of some nifty DAX and replicate the same behavior.


First, let me show you the existing behavior in Power BI. For demonstrating that, I have a dataset that has the count of people with diabetes as well as the population by State in the US.


I made a simple bar chart on the left that shows the diabetes count by state, and another scatter chart that shows the Diabetes % (population adjusted value) and the population by states (special thanks to the folks at PowerBI.tips for the lovely layouts). Notice how the dots in the scatter chart get filtered when I click on the bar chart.

1 Original behavior

Now, follow the steps below so that you can replicate the highlighting functionality:-

1) Our intention is to create a cross-filtering effect for the State in the bubble chart. For that, we will have to create a disconnected table for State (say StateSlicer) and also create another table called IsSelectedFlag that has just 2 values – Y and N. Create two calculated tables with the formula below:-

StateSlicer = State

IsSelectedFlag = UNION ( ROW ( “Flag”, “Y” ), ROW ( “Flag”, “N” ) )

2) Create a measure called DiabetesSlicer that will display the diabetes count for the States in the disconnected table.

DiabetesSlicer =
    SUM ( Diabetes[Number] ),
    INTERSECT ( VALUES ( State[State] ), VALUES ( StateSlicer[State] ) )

You can now make a bar chart from the StateSlicer and the DiabetesSlicer.


3) The next step is to make a scatter chart for Diabetes% (which is just the diabetes count / population for the state) and Population by State. The important thing here is to make a measure for the diabetes count that will show both the selected and unselected values. The Flag can be used as a legend for that purpose, but before it can work, we first need to write some DAX as shown below.

Sel_Diabetes =
— Diabetes count
VAR Diab =
    SUM ( Diabetes[Number] )
    SUMX (
        –iterating for each value of the flag
        VALUES ( IsSelectedFlag[Flag] ),
        — calculating the selected value of the flag       
        VAR SelVal =
            CALCULATE ( SELECTEDVALUE ( IsSelectedFlag[Flag] ) )
            SWITCH (
                –if it is Yes (meaning only the selected values), display DiabetesSlicer
                — DiabetesSlicer shows value only for selected state in bar chart               
                “Y”, [DiabetesSlicer],
                — if no (for values not selected), subtract from the total       
                — for unselected values, DiabetesSlicer is blank, but Diab is always displayed        
                “N”, IF ( Diab <> [DiabetesSlicer], Diab – [DiabetesSlicer] )

It is advised to use the same technique for Population also, especially if you are having other charts. But in this scenario, where I only have a bar chart and scatter chart, I can get away with just changing one of the measures of the scatter chart (which is the Diabetes% in this case). Create the Diabetes% measure now

Sel_Diabetes% =
DIVIDE ( [Sel_Diabetes], SUM ( Population[Population] ) )

4) Now create a scatter chart with the State from the original state table, flag in the legend and the Population and Sel_Diabetes% as the Axes.


5) Now you should be able to see the highlighting functionality. I also added some slicers and the metrics in the left hand side, as well as a simple report tooltip (just because I LOVE this feature). Also, change the default colors for the Legend, ideally choose a dark color for Yes and a lighter shade of the same color for No so that it looks natural.

2 Highlighting behavior

Feel free to download the pbix file from here and play with it.

Posted by SQLJason in Power BI, 0 comments

Power BI Licensing – Cost Forecast Guide

March 12, 2018

The other day, one of my enterprise customers asked me if there were any guides or documentation to forecast the cost for Power BI service licensing (Pro and Premium) for 3 years based on user growth. I did mention that we can use the official Power BI Premium calculator to calculate it for each year and then use the sum. However, after the call, I took this as an opportunity to show the customer how versatile Power BI is, and decided to create a report for this customer’s particular need.


I thought it might be useful for some enterprise customers to see what the total cost is going to be for 3 years, and decided to share it here. You can use this guide to see some of the additional information like:-

  1. Forecast the growth in % for Pro, Frequent and Occasional users
  2. Get the total cost for 3 years based on the growth
  3. See the per user cost for each year
  4. Also, see the estimated utilization of the last Premium node, which will give you a good idea on whether you are close to upgrading or not

That said, the report is built using the current (as of 3/12/2018) formulas for the Power BI Premium calculator (if it changes, I will try my best to keep it up-to-date) and it might be a good idea to double check your numbers against the original Premium calculator too. Without further ado, the report is given below, feel free to explore.

As always, let me know your feedback or if you have any questions on the same.

Posted by SQLJason in Power BI, 3 comments

Display Last N Months & Selected Month using Single Date Dimension in Power BI

March 2, 2018

It is Friday, and I thought of writing a quick tip on how you can use the same date dimension for displaying the last N months (say, in a bar chart) and the data for selected month (say, in a card visual). Most of the techniques for doing the same use a disconnected date dimension along with the regular date dimension; however, using the same date dimension as a filter has the additional benefit that you can use all of the other measures also without any changes in the same report.

imageBefore I show you the technique, let me show you an example of a finished report. Below, you can see that I have displayed 3 regular measures based on the selected month (Sales for selected month, Sales YTD and Sales Last Year) along with a bar chart that shows the sales for the last N months based on the selected month (special thanks to the folks at PowerBI.tips for the pretty layout).


Follow the steps below to recreate the same:-

1) For the purpose of this post, I am using a very simple model – a Sales table (with just Date and Sales) and a Date table.


2) Create 3 measures as shown below, and then add those 3 measures in the report along with a month slicer as shown below. You can change the month in the slicer and verify that the measure values change for the selected month.

Sales (Selected Month)
= SUM ( Sales[Sales] )

Sales Last Year
= CALCULATE ( SUM ( Sales[Sales] ), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) )

Sales YTD
= TOTALYTD ( SUM ( Sales[Sales] ), ‘Date'[Date] )




3) The next step is to make a measure that will display the last N months. Let us create a What If parameter called N with values from 1 to 24, and increments of 1. Place it in the chart as shown below


4) The main step for this technique is – create a measure that will display the sum of sales for the last N months. It is important to know that putting the Month from the Date table will not work, so what we are going to do is create a month column in the Sales table and then use that as the axis for the bar chart. Create the 2 calculated columns in the Sales table (MonthYear and also MonthYearNo for sorting the MonthYear column), as well as a measure – Sales (last n months).

= RELATED ( ‘Date'[MonthofYear] )

= RELATED ( ‘Date'[MonthYearNo] )

Sales (last n months) =
VAR MaxFactDate =
    CALCULATE ( MAX ( Sales[Date] ), ALL ( ‘Date’ ) ) — ignore the selected date filter, and find the max of date in Sales table
VAR FDate =
    ENDOFMONTH ( ‘Date'[Date] ) — get the last day of the month selected in the date filter
VAR Edate =
    EDATE ( FDate, – [N Value] ) — get the last day of -N months
    IF (
MaxFactDate <= MAX ( ‘Date'[Date] )
&& MaxFactDate > Edate,
        CALCULATE ( SUM ( Sales[Sales] ), ALL ( ‘Date’ ) )
    ) — if the date in the fact table is between the last N months, display Sales, else nothing. Note that we are ignoring the date filter, only respect the date in Fact

Update -3/3/2018

Owen Auger (twitter) has come up with an easier formula, use this one instead of mine –

Sales (last n months) =
    SUM ( Sales[Sales] ),
    DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – [N Value], MONTH )

5) Now create a bar chart with MonthYear on Axis and Sales (last n months) on the values, as shown below.


At this point, you can change the month in the filter, as well as the value of N and see the bar chart change (as well as the other measures). 

Posted by SQLJason in Power BI, 6 comments

Password Locking Publish to Web in Power BI

February 28, 2018

*WARNING – This technique will not provide data security (only obscurity), so please don’t use it with your confidential data. Always use your Pro/Premium license to share confidential data.

Last week, I had published a Power BI contest where you had to solve some clues to unlock a hidden tab. The underlying technique was inspired from a community post but I used a totally different approach using the new Sync Slicers feature. In this post, I am going to show you the technique behind locking a tab with a password/code while using the Publish to Web feature in Power BI.



Before I start, I want to make sure that everyone understands some points clearly:-

1) There is a reason I am mentioning this technique as “locking” and not “protecting”. This technique does not provide security, and Microsoft clearly states that you should not be publishing any confidential information using the Publish to Web feature. Even though it might look like it does the job from the outside, there could be a method unknown to us in which the code can be obtained or even worse, the entire data can be obtained. Lukasz (Power BI PM) summarizes it pretty well in the tweet below




2) If you want security, please buy a Pro license (or a Premium license depending on your consumer base), it costs less than 3 cups of coffee per person a month. And with new and more strict regulations like General Data Protection Regulation (GDPR) coming, the fines for non-compliance can run into tens of millions (if not more). Please remember that Power BI changes every month; a new feature or a change in the way it works currently can easily render this approach useless and you will not want your confidential data to be exposed to the whole world. The only intent of this post is to talk about this approach and see if it might inspire other creative approaches like the Power BI puzzle I created (and not to bypass any licensing restrictions).

Now that everyone has read the warnings, let us get to the fun stuff. First, see how the codes were used in the puzzle to lock a hidden tab and how the hidden tab showed the right results when the correct code was selected.


To demonstrate this technique, I will create a simple report with only 2 codes, but you can easily expand this to multiple codes. Follow the steps below:-

1) For the purpose of this demo, I just connected to Wikipedia and got the population by states.


2) Along with that, I just entered data directly into the power bi file and created 2 code tables. The code tables will contain your codes and can be anything. To make it simple, I just made it as 2 letters as shown below.


The more entries you have and the more code tables you have, the larger your permutations and hence, the harder to crack your code.

3) Create a password table with 2 columns – with one column as the correct code, and the other one as 1. This table will be used to check against the selected codes for a match with the help of a calculation. 


4) Now that we have our code tables (Code1 and Code2), password table and the data table, let us proceed to the reporting canvas. Make a measure called Lock which will check if the selected values of Code1 and Code 2 match the password.

Lock =
VAR Code =
    SELECTEDVALUE ( Code1[Code1] ) & “,”
& SELECTEDVALUE ( Code2[Code2] )
VAR Pswd =
    CALCULATE ( FIRSTNONBLANK ( ‘Password'[Code], 1 ), ‘Password'[Access] = 1 )
    IF ( Code = Pswd, “Unlocked”, “Locked” )

5) Make another measure that will display the sum of the population only if the correct codes are entered.

TotalPopulation =
IF ( [Lock] = “Unlocked”, SUM ( ‘Population'[Population] ) )

6) Now create a tab called Hidden. Use the new measure that we created in step 5 (Total Population) to create a bar chart by states and a card visual on the top. You will see that no data is displayed and that is because we haven’t selected any values for the Code tables (Code1 and Code2)


7) Create a bookmark with this view and rename it. Click on the three dots next to the bookmark, and makes sure that you uncheck the Data tab. This will ensure that the bookmark will respect any filters or slicers.


8) Now create a new page where you can display the Codes. Display Code1 and Code 2 as slicers. Also, display the Lock measure as a card visual.


9) Sync both the slicers (Code1 and Code2) with the Hidden page. Make sure that the slicers are not visible in the Hidden page though. The selections are shown in the image below. By the end of this step, whatever codes you select in this page will also be passed to the Hidden page. 


10) Now all that is left to do is to put a rectangular shape on top of the card visual (with some transparency), and add a link to the bookmark we created.


Also make sure to hide the Hidden tab.


11) Now we can publish this to Power BI, and then Publish to Web. See the end result in action below.


NOTE: For some reason, the initial password gets cached and exposed in the trace and this is another reason why you will not want to try this with your confidential data, and only use it for fun projects. That said, once I changed the password and republished it, the new password did not get exposed and it was still the old password that was getting exposed in the trace.

Posted by SQLJason in DAX, Power BI, 0 comments

Announcing the winner of the Power BI Game (as well as the solution)

February 20, 2018

Since yesterday, we had over 200 unique users try out my Power BI Game and as of now, 6 users have cracked the puzzle. I am pleased to announce that we have a winner for the contest and he responded with the answer 64 minutes after publishing.


The winner of the contest is Cory Tollefson (twitter) from Minnesota and he gets an Amazon gift voucher worth $100 for his efforts.

Spoiler Alert : Those who want to read how he solved it, click here. For others who still want to keep trying, but need an extra clue, I will add individual spoilers for the clues below

“I think I solved your Power BI game. Pretty cool but I’m a little ashamed at how many times I clicked the Red Herring clue line thinking I was missing something or it had two meanings.

First clue was END and it started with birthday cake into the Marilyn Monroe pillow because of Happy Birthday, Mr President which led to the presidential seal on the door. She sang it for JFK so took the letters from his name.

Second clue was the water bottle with the muppets that led to Kermit and then Steve Whitmire (had to Google him though) and took the letters from his name.

Third clue was the book to the upside down Eye of Providence which was a bit of an educated guess. That led to Freemasons square to the Mona Lisa for the 3 digit code of 474.

Last one was tricky until I went to full screen and saw the words on the mug matched some of the souvenirs. Mug to Venice souvenir to Crown Medallion to Amsterdam souvenir to Lisbon souvenir to map of Germany. Then took the digits from the year the Berlin Wall came down for 189.”


As stated in the rules, there are no other prizes but I want to recognize all the 6 people who have solved the puzzle along with their timings (note that the timing is based on the time of publishing (10 am EST 2/19/2018) , and not the actual time taken to complete the puzzle) –

1) Cory Tollefson (twitter) from USA – 1 hr 4 mins

2) Sam Lester (twitter) from Germany – 1 hr 9 mins

3) Shannon Lowder (twitter) from USA – 2 hr 9 mins

4) Haydn Carline from UK – 5 hrs 25 mins

5) Philip Chan from Canada – 16 hrs 52 mins

6) Ken Geeraerts (twitter) from Belgium – 22 hrs 51 mins

Great job, everyone! 

Now, I will give out individual spoilers below:-

Spoiler Alert : Red Herring

If you had turned on the white light switch (or if you knew Greek alphabets, you would have understood the words on top of the door – DO NOT ALLOW THE EYE TO FOOL THE MIND, which was basically a warning to not click on the Eye of Ra medallion and go on a wild goose chase. The wild goose chase here would be –

click on the Eye of Ra medallion which will instruct you to click on Ra’s likeness, the statue

click on Ra’s statue and it will ask you a riddle. A simple google would have given you the answer as Day & Night. This was apparently a riddle asked by the Sphinx to Oedipus. Try to find something related to Day & night within the room.

click on the Starry Night painting (which is related to Day & Night) and that will instruct you to click on the words on top of the door, and that will confirm that all the work you did was for no use.


Spoiler Alert : First Clue

Click on the birthday cake, and google the message. You will find that the words are actually taken from a birthday song sung by Marilyn Monroe for John F Kennedy.

Click on the Marilyn Monroe pillow and then click on the Presidential Seal medallion hung on the door. The code is the 2nd, 4th and 6th letters from JFK’s last name – END


Spoiler Alert : Second Clue

Click on the bottle which has the picture of Muppets on it. Find something related to the Muppets.

Click on the green “Kermit the frog” toy and note that the commentary asks if you know your Muppets well.

Click on the first photo frame on the left wall, which is of Steve Whitmire, the voice of Kermit the Frog. The code was the 1st, 2nd and 3rd letters of his last name – WHI.

There was an additional clue – “Legends of Rock. Or are we?”. The other 2 are in fact legends of rock – Mick Jagger (The Rollin Stones) and Kirk Hammett (Metallica)


Spoiler Alert : Third Clue

Click on the Dan Brown book on the table – a book on lost symbols.

He also talks about secret societies and symbols, and the medallion with the upside down Eye of Providence is a symbol of FreeMasons. Click on it.

Then click on the Square tool lying on the ground, which again is a symbol of FreeMasons. That will ask you to click on the Mona Lisa painting (which again was referred in Dan Brown’s books) and the code will be revealed behind the painting – 474


Spoiler Alert : Fourth Clue

Click on the white coffee mug with the names of the European cities written on them.

Then click on the souvenir for Venice (on top of the door), London (the middle medallion in the shelf with Queen Elizabeth II’s logo), Amsterdam (left most souvenir on top of door), Lisbon (the blue tile souvenir on top of the door) and Berlin (the German map showing East Germany and West Germany).
The code is the 1st, 3rd and 4th digits of the year in which the Berlin Wall fell (1989) – 189


Hope you had as much fun playing this as much I had making it for you! Let me know in the comments how was your experience.

Posted by SQLJason in Power BI, 1 comment

A Power BI Game… and a chance to win some prizes too!

February 19, 2018

Update (2/20/2018) – This contest is now closed. Winner (as well as how to solve the puzzle) announced here.

Over the years, I have participated in my fair share of Power BI contests and for most of them, I bugged a lot of you for votes (so that I can go to the next round). Thanks to your love and support, I have won a couple too. I have put those days behind me, now that I have joined Microsoft (and employees are barred from participating in any kind of contests that are organized by Microsoft). That said, I always wanted to do something back for the community, for everything you have done for me, but had no idea how to implement it. That is when I saw a community post that was shared on Twitter, and I decided to make a fun game out of it using a modified technique (more details on the inner workings will come in a following post).




The rules for this game are simple – just unlock the codes on the first tab to reveal a secret tab with details on how to claim the prize. There is a second tab that has a puzzle, solve it to reveal the codes. Click around on the items around the room in the second tab for clues, and if you click on the right ones, they will tell you what the codes are. Your observation skills as well as your general knowledge skills will be put to the test here. Rules are explained in detail on the third tab of the report. Read them carefully. The first person to solve the challenge according to the rules will win the prize. The contest may just last for minutes or may last for 1 week – depending on how fast you solve it. If no progress is made by end of Week 1, I might start throwing extra clues on twitter, so stay tuned! I will also announce the winner on my blog once we are done with the contest.



Maximize the report for a better experience before you sit down to solve the clues. As for people who are curious on how this was done, the main concepts are:-

1) Bookmarks

2) Sync Slicers

3) Dynamic measures

4) Hide tabs in Power BI


May the best man / woman win! Smile

Posted by SQLJason in Power BI, 0 comments

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.




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.



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.



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.



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.




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




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

SalesAlert =
    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
= ( 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




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.




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



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.




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.




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.




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.




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.




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



  • 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.



  • 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. 



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


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.


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



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.




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




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. 




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




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.




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




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.


  • 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




  • 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.




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



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.




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, 7 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?


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.




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

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.


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.


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, 8 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.


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