Year: 2017

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