Creating New Teams in Microsoft Teams and Power BI App Workspaces (The Classic to New App Workspace issue)

Microsoft teams is your central hub for teamwork so we can all collaborate in one place. its an offering of Office 365.

Microsoft Teams are part of Office 365 Groups
  • You can either, Make a team and connect it to an existing office 365 Group
  • However Creating Teams rather that Office 365 Groups is probably the best solution

Teams can be private or public. They will have an owner and Members and guests.

When you create a new Team channel. For example

And then move to your Power BI Service you will find this …

An App workspace that corresponds to the team. In fact, you have lots of App Workspaces. Only some are actually relevant to power BI. Teams does not create the Power BI workspace. Teams actually creates an O365 group and Power BI uses the O365 groups created

You either

a. Want to work with it as a proper app workspace and add reports and Dashboards

b. Really don’t need it in Power BI and want to get rid

Lets look at these options in more detail

Use the Teams Workspace as an App Workspace

I have data on the training courses offered so I have decided I want to use this app workspace

the Report is published to the App Workspace

Here are the menu items for the Report created over the Teams app Workspace

And here are the menu items for an App workspace that was specifically created in Power BI

Notice that Settings and Access aren’t there. This means you cant collaborate with your team in the Workspace or look at the workspace settings. Why is this?

When New Workspaces are created in Power BI Via Teams, the App Workspace is created as Classic. Not the new Workspace. New Workspaces aren’t tied to the Office 365 Group.

In order to collaborate in classic you need to click on … and go to Members. This takes you to Office 365 and you have to add individual members from here.

We don’t want the Workspace to be tied to Office 365. and we want to change the App workspace from Classic to New

Are you an Admin / Owner of the team?

No. I am a member of this group. Because of this there are no other options to look at in here.

To Upgrade to the New App Workspace you need to be a Group Admin. If the person who created the Teams channel isn’t a regular user you may want to see if you can get yourself admin permissions via the Teams owner


And change from Member to Owner

Now as an owner you can Edit this workspace

You can now See Advanced. click on this to get to the upgrade option

Select Upgrade Now

Once the workspace is upgraded it is no longer tied to the Team or Office 365 and can be used like the standard Power BI New App Workspace.

Delete the App Workspace as its not required

The Team has been created and its now in your Power BI App Workspaces list. Its not required and its just adding extra information into Power BI that is not needed

You are the Owner of the Team and can see this when you go to Workspace Settings


DO NOT DELETE THE WORKSPACE

If at this point you delete the Workspace, its still connected to Office 365 and you will delete everything.

Use the information above to Upgrade the the New App Workspace before deleting.

The App Workspace must be disconnected first by not being a classic Workspace.

And if you want to try this out. test with a small team first. We have done numerous tests and after upgrade the Delete hasn’t removed anything but better to be safe than sorry.

there is a Post on this in Power BI Ideas

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36193693-disable-or-prevent-teams-from-autocreating-app-wor

Power BI New Visuals Feb 2020 Release The Funnel and Pyramid chart by XViz

lets again have a look at the Adventureworks visuals using the Adventureworks warehouse as the sample data set

Funnel and Pyramid chart by XViz

Great for looking at Lead to Customer Conversion process for Sales analysis. Also great to visualise business functions like Organisation Hierarchies.

a very simple example is to look at Sales by Education

Its very possible that those actually in a College activity are providing us with the most revenue (Partial College)

YTD Internet Sales was change to Show value as Percent of Grand Total and in Format. Labels were turned on.

You can also change it into a Pyramid

as an example, the sales team have their own data of Prospective Buyers and the code changes when they convert to actual customers.

In Power Query Editor, a New Column has been added to track conversions

A custom column was also added and set to 1 for an easy customer count.

We only have 4% of customers as leads at the moment. Over 51% have converted to Large Scale Projects with us.

1 % have had one call from us so the Sales team need to get more leads and make more calls.

Click on the report Advanced Editor

And you can add Conditional Formatting.

However, there seems to be an issue here because, we have 6 in the range but the colours only come out in three groups which isn’t correct

Power BI March 2020 New Drill through Functionality

Currently we have lots of visuals on the report pages and the only way to know that they drill through to other reports is to hover over them

In the March 2020 release there is new functionality in the form of buttons

Add a blank button

This button is now set to drill through preview and you have chosen the drill through page. On normal drill through, everything this that is available is shown which is difficult when the report structure is complex. This allows you to target and manage the drill through structure.


Nothing selected
31 days selected

And this drills through to the detail reports for 31 days. Note that the tooltip in Desktop tells you how to drill through. Lets see this in Service.

Change the text on the button

Lets customise the button text using conditional formatting

the data item behind this drill through is ‘Resolved in’ and then it contains number of days categories.

Create a new measure

A new measure has been added to the dimension table containing the Resolved in Item

String for Resolved in Button logic = IF((SELECTEDVALUE('dim table'[Resolved in ],0)==0),"See Resolved in Details", "See Details for " & SELECTEDVALUE('dim table'[Resolved in ]))

If the selected value is null, set it to 0. So if its zero, show “See resolved in Details”. If its not zero then it can be displayed

Add New Measure to the Button

Resolving a noisy drill through with Drill through Buttons

This visual in this Drill though report has three Drill through’s for Incident. Then the stage of the incident. Finally the Business Property Drill through. All are valid but we would prefer to Not have the stage here because this should only be accessible via Incident.

Add two buttons. Link one to the Incident Drill through report. The other to the Business Property Drill through report

We want the button to be dark red when its active.

Create a new measure for the Incident in the incident Dimension

String for Incident in Button logic = IF((SELECTEDVALUE('dim BPIncident'[Incident Ref],0)==0),"See Incident Details", "See Incident Details for " & SELECTEDVALUE('dim BPIncident'[Incident Ref]))

And add this as conditional Formatting to the button Text

Create a new measure in the Business Property Dimension

String for Business Property in Button logic = IF((SELECTEDVALUE('dim BusProperties'[BP Number],0)==0),"See Business Property Details", "See Business Property Details for " & SELECTEDVALUE('dim BusProperties'[BP Number]))

And add this as conditional Formatting to the button Text

And now you should get drill throughs that the user can immediately see and use. Avoiding the stage drill through because it doesnt match what we want the journey to be

Note that the buttons are greyed out

Weaknesses

  • The button takes up more space.
  • Currently you have the drill through button and the drill through on the visual. This could be confusing to the user. It would be great if you could turn the drill through on the visual to off

Power BI Switching Tenants and User Accounts (Emails) but can only see Dataflows from previous tenant

Recently we did a training course with two developers in our own tenant under two training accounts. the training was done on their own computers but they logged into our tenant with our training details.

Then they moved back to their own tenant in Power BI Pro with their own details to start creating a data flow.

They have an App workspace and are both Admins in the workspace

Developer 1 Created Workflows within this App Workspace.  Developer 1 can open these data flows but Developer 2 doesn’t have access because they are not the owner which seems like the correct logic. they should only be able to use them in Desktop.

However, when both Developer 1 and developer 2 both go into Power BI desktop and attempt to connect to the data flow (Both Desktop and Service is connected to the company account)  they see a dataflow that was created our tenant when we were doing the training course. the new data flows cant be seen by either Developer.

They have both tried to refresh the view (Above) but this didn’t work. This does seem to be something that happens with the users log out and use a different account in a different tenant

How to resolve the Issue

Use Files Options and settingsData source Settings

then find Power BI Dataflows in Global Permission, then Clear Permissions of it.

When you Connect to the Power BI Dataflows again you have to sign in. Sign in as the current user, then you can see the Dataflows of your current account.

So if ever you are working in more that one tenant, remember this fix if you can only see the dataflows from the previous account

Buying and Pausing a Power BI A SKU

I want to set up Power BI Dedicated Capacity specifically for development and testing Premium features.

the Power BI Premium SKU’s should only be used in production. We want something that we can pause when not in use and help us to research all the Premium capabilities.

Thankfully we can go for the A SKU (1 through 6)

  • A4 provides the same functionality as the P1 SKU
  • A2 is the same as P2
  • A6 is the same as the P3

The only difference is that you don’t get full user access like you do with Premium. Only users with Pro accounts can access the content (Which makes sense because we haven’t moved to Production)

And you can Pause when not it use. This is fantastic and will save lots of money in the development and testing phases

The question is, which SKU do we require. I know I need to test Paginated Reports and this is only available from A4 so clearly we need the SKU that corresponds with P1

Buying A4 SKU

Using an account that has at least capacity admin permissions in Power BI.

Log into Azure and Search for Power BI embedded

Here I have decided to choose our training subscription and training resource group.

 The capacity admin must be a member user or a service principal in your Azure AD tenant. I have selected myself for this role for this development Power BI area

At this point you need to choose the resource size and I know that we need the A4

If we left this one the whole time it would cost £4,467.41 for the month but obviously we only need this when we are testing Premium functionality so we would be pausing this most of the time.

Its extremely important that this is paused after use

I have added a few tags to this service before creating

Go to resource because we want to immediately Pause it

And Pause. I will only unpause the resource when I want to set up an App Workspace on this test capacity to check the Paginated report and other functionality.

The A4 SKU Will cost around £6 an hour so ensure you plan your development time effectively

Add a help video to your Report visuals to engage users

I have a drill through in my crime reports so you can drill through to the data from the top level report. However, sometimes I am aware that users struggle with these cool Power BI Features.

A new feature is that if you have a drill through, you get a Right click to drill through message on the tooltip. Is that enough? for some people its not. Some people don’t understand the concept of drill through. They may need more visual help.

And its all about making sure the users are getting the full benefit of Power BI so its worth adding a few extras

How about creating a short video of the drill through in action that you can add to the visual.

To create a video of the drill through I am going to use ezvid as its free for this demo. Camtasia is the solution I want to move to because this will save the file as an animated jpg.

I have downloaded from the following site

https://www.ezvid.com/ezvid_for_windows

And in order to record a small part of my screen I have had to set my monitor to display at 100%

Ezvid creates a final.wmv file

Convert wmv to jpeg

the video needs to be a jpeg in order to work so I need to convert the file I have created

For the purposes of this example I have used convertio

https://convertio.co/wmv-gif/

an online tool for conversion.

and I have simply converted and downloaded the file to a gif.

Back to Power BI

In the report, add a new page. I am calling in Drillthrough tooltip

Right click on the worksheet and hide the page

next in page format, go to page information and Set Tooltip as ON

Go to page size and set type as tooltip

Next in Page format go to page background and add image (I am adding the video that I created. make sure you have the location of the jpg to hand

Make sure that transparency is at 0% and the Image fit is Fit.

You should be able to see your gif moving in your tooltip page which is ready to be used in the top level report

Go back to the visual that will be using the drill through animation

In format for the visual go to Visual Header and scroll right down to Visual header tooltip icon

Tip. People are missing this is the list, note that there are two scroll bars and ensure you have scrolled down to the bottom with the inner scroll bar.

A little Question mark appears at the top of the visual

We now have a new option in the format list. Visual Header Tooltip, where you can add written instructions on your visual

but we want to do something fancier and have the video clip playing

Change the Report page from none to the tooltip page and now when you hover oper the question mark a how to video will play.

This is a great way of showing off some of the more involved concepts of how to use Power BI.

Power BI October 2019 New Visuals and Publish to Web

To start to have a look at these new visuals I have downloaded some crime stats for my local area

I want to try out some of the new visuals in Power BI in the October Release whilst also doing my first public upload of a report. the visuals in the XViz suite can be used for free, you can purchase them with full functionality. I am using the Free Versions of these visuals

Hierarchy Variance Table (xViz Visual)

Compare measures within a hierarchical table and see the variance.

the first thing I need is a hierarchy which I created using Crime and Status.

E.g. Possession of Weapons

  • Offender Fined
  • Court Result unavailable
  • Under Investigation

I went to the market place and selected the new hierarchy Variance table

to do that I simply dragged Outcome Status over category to create a hierarchy.

Crime is simply a metric I added 1 for each row in order to Sum

Time period is our date Column

Note all the information you get from this new visual. A spark line and an indicator along with the metrics that the visual creates for the visual

In Formatting you can also go into format Number formatting and switch on Semantic Formatting that colours the cells based on positive or negative values

You can also right click on the items in category and Get access to options like what to display, sort and rearrange the hierachy.

So what does this tell us? I’m going to look at Robbery.

the first thing to note is that if you have a Filter on Year, If you select a Year the data disappears and you are left with just Baseline. If you have a Year Filter, remember to ensure Interaction is off for this visual.

we have 9 records in total. It seems to be taking Baseline as the total for 2018

For target it seems to be taking the data from the first quarter of 2019. This doesnt make sense to pitch 2018 against 2019 First Quarter

Lets see if Vehicle Crime makes any more sense

Again baseline appears to be the total for 2018. target of 4 could be Quarter 1 and Quarter 2? Missing Quarter 3. So the question is, How does it arrive at the baseline and target?

Unfortunately as yet, there is nothing on line that explains how these metrics are calculated which means we have no way of knowing at the moment. Until this information is clearly provided I would say that this visual is not fit for purpose.

I certainly wouldn’t want to demo the visual for a customer to say, what does baseline and target relate too? At present I wouldn’t be able to answer that question. If you have an idea please let me know.

Linear Gauge Table (xViz Visual)

Compare a Value (resolved) to a target goal (% Number of Crimes) Ideally we want 95% of these Crimes resolved in the data set

This is very straightforward. For Actual value I want resolved crimes and the maximum value will be the number of Crimes.

I have also set Condition to On in formatting where under 60% is red. everything over 90% is blue and everything in between is amber.

Straight away we are in the red for number of crimes solved

Tag Cloud (xViz Visual)

I use the Wordcloud for Twitter data where I have already used an Azure text API to create me a table of Keywords. tag clouds are very similar to Wordclouds and allow you to look at tags of words in the data

Tag cloud takes advantage of extra-Miler tags within the data set (The words that mean more within your data set) tag cloud finds these ‘Keywords’ and displays them.

In this case I am using category of crime

category = category

Size Value = Crime (Metric)

Key Influencers

Its not new but I thought Key influencers would be a great visual for this report. I have added a conditional column against outcome to ensure we only had a few conditions to choose from

For No action taken, No suspect identified is the top influence. Further questions could be, why is this the case? Should we not be doing better on finding the person who committed the crime?

Publish to Web

I now have all the visuals I need to get the information out their. its not private data so I can publish to the web. I want to publish to this wordpress site

First of all, Lets publish to My Workspace in Power BI Service

then log in to check that the Report is in My Workspace

Publish to Web

Once there, go to File and Publish to Web

For the time being I have copied the Link and HTML

Install WordPress Plugin

You can install the WordPress plugin for PowerBI

However you need to be on the Business Plan to do this. I’m not on the Business plan. Its £20 per month.

So to end this post, here is the link to the public report

https://app.powerbi.com/view?r=eyJrIjoiZTY5YWZkOTctM2NkOC00MGZmLThkMjctYjAzOWRjMjdmNGE3IiwidCI6IjE2OGMxZmUzLWE4NDEtNDliNS1iNjkyLTdiMzEzMmMwYTk5NyIsImMiOjh9

The Key Influencers isnt available in Publish to Web Format

You should always be aware when publishing to we that there is no security in place so never publish to web any information you are not happy for everyone to see

Power BI Streaming Data sets (And Push Data Sets) Part 2

We are going to add some new information into the data set

In Power BI Service, Click Edit against the #Taskmaster dataset

I’m actually going to add 7 more fields

  • Description, Sentiment and Name are Text
  • CreatedAt is a date
  • Count, Favorited and followersCount are Numbers.  Click Done

Return to Microsoft Flow

Edit the flow and go to add rows to a dataset

  • Count is an Expression
  • It is simply set as 1

You can Test and Save

Any new data that comes through will contain these data items

Creating sentiment is slightly more involved but lets go ahead and create sentiment

After Detect Sentiment update your flow with the following logic

Create Sentiment which is Neutral. If Score is over 0.7 then Positive. If under 0.3 then negative

We can add this to our data set


We now have lots of new information to use in our streaming reports

Back in Power BI Service

I’ve attempted to add a new Custom Streaming Tile to the dashboard based on a Line chart to look at the count of records

Unfortunately this Streaming visual doesn’t seem to work and immediately I can see a fatal flaw with using streaming data set visuals for this kind of data

These visuals are for data that pulls through almost constant stream of data. They are not for data that has a feed that at some points doesn’t have much data coming through. You need to have lots of data in the time frame of the streaming dataset (For example 60 minutes).

I have the #Taskmaster Stream set up for Historical Data Analysis

when Historic data analysis is enabled, the dataset created becomes both a streaming dataset and a push dataset

streaming dataset, has no underlying database

A Push data set

For a push dataset has a few limitations on how much data can be pushed in:

  • 75 max columns
  • 75 max tables
  • 200,000 max rows stored per table in FIFO dataset
  • 5,000,000 max rows stored per table in ‘none retention policy’ dataset

The data is then stored in Power BI and we can actually access it from Desktop.

This means I can create some really nice reports against my taskmaster data set

You cant create Hierarchies of calculated columns over the Push data set. Only Measures. You are very limited on what you can do. The Key Influencers visual is also unable to work with push data which is a shame because this is the perfect visual for analysing positive and negative tweets

I should have brought date across as a date in the first instance because Month is now just a number and I cant change this in Power BI. We have date but only against the very latest data

Time was already in the data set but this consists of date and Time which you cant reset to create a date hierarchy

I cant add day names to the day number(Taskmaster is on on Wednesday so I expect the levels to go up then)

So the push data set is fairly simple to set up but its incredibly limiting to what you can do

Our initial page by Month. I needed to Add Month Name into the Flow

Next I drill through on the 9th of October. the name of the day would be great because taskmaster is on a Wednesday

Finally we drill through to the hourly tweets.

As you can see, We have a few negatives in the data set which, on reading aren’t actually negative. The Cognitive API doesn’t recognise sarcasm for instance.

There you go, We started out with a streaming data set and ended up with a push data set.

The push data set doesn’t need a refresh which is them main reason to go for them.

One last quick thing about this push dataset. When I attempted to republish the report I got the following error

To get past this error I had to delete my report in Power BI Service before trying again. I have never seen this kind of conflict before and I’m assuming its an issue with the Push data set

Power BI Service Data Lineage View

I was logging into Power BI this morning when I saw this exciting new feature

we are always looking at new solutions to provide good data lineage so this is well worth a look

Data lineage includes the data origin, what happens to it and where it moves over time. Data lineage gives visibility while greatly simplifying the ability to trace errors back to the root cause in a data analytics process. 

Wikipedia

I have an App workspace set up for Adventureworks so lets have a look at Lineage using this project

Column 1 is my data source. I can see I’m using a local database and I’m also using an xlsx spreadsheet to bring in data.

In most of my projects I’m working on the ETL in Data factory, transforming data in Stored Procedures etc. for example, for a social media feed, I have a logic app that moves tweets to an Azure Data Storage NOSQL table. Data Factory then transfers this data across into a central Azure Data Warehouse. The Power BI Lineage would pick up at the data Warehouse stage. It wont take into account that there is a lot of work previous to this

Column 2 is the data set in Power BI

Column 3 provides Report information

Column 4 displays the Dashboards

You can click on a data flow node to drill down into more detail

Currently you cant go any further to look at the data items

Click on the Link icon to see the data flow for that item. In this case the Report.

This is a great start but there definitely needs to be more information here to make it something that you would want to use as a proper Data Lineage Tool

  • It would be good to see the fields in each Entity for the Data Sets
  • As an extra, it would be great to see what fields are being used in Measures and calculated Fields
  • Reports – For me, Id like to know for every page in my report
    • What field am I using from the data source
    • What calculated columns I have created (Even better with the DAX Logic)
    • Any Name changes from Data Source to Power BI
    • What measures I have created (Even better with the DAX Logic)
  • For the Dashboard, What items I am using in the dashboards (Fields, Measures, Calculated Columns
  • An Important part of data lineage is getting and understanding of the entire process. This includes data transformations pre Power BI. If you cant do that in here, it would be great to be able to extract all the information out so you can use it in some way with your other Linage information to provide the full story. for example:

Azure Data Catalogue

Azure Data Catalog is a fully managed cloud service. Users can discover and consume data sources via the catalog and is a single , central place for all the organisation to contribute and understand all your data sources.

https://eun-su1.azuredatacatalog.com/#/home

I have already registered Our Data Catalog, and I have downloaded the desktop app

As an Example I want to connect to Azure Table Storage (Connect using Azure Account name and Access Key)

At this point I’m registering everything in the storage table. then I can view the information in the Azure Portal.

You can add a friendly Name, description, Add in expert (in this case me). Tags and management information

I have added Data Preview so you can view the data within the object. there is also documentation and Column information to look at

In the data catalog you can manually add lots of description to your tables along with documentation.

This is great for providing lots of information about your data . You can explore databases and open the information in other formats (Great if you need to supply information to another Data lineage package

I will be having a look at the Azure Data catalog in more detail later to see how it could help to provide full data lineage

Azure Data Factory

Data factory is the Azure ETL Orchestration tool. Go into Monitoring for Lineage Information. However, there doesn’t seem to be a way to export this information to use. Data Factory wont take into account the work done in, for example a stored Procedure

Again this is another area to look into more.

Stored Procedures

When you use Stored Procedures to transform you data, its harder to provide automated Linage on your code. There are automated data lineage tool for SQL out there, but it would be great if there was a specific Tool within Azure that creates Data Lineage information from your Stored Procedures

Azure Logic Apps

Data for my project is collected via Logic Apps before being Processed into an Azure Data Warehouse.

Essentially, we need out data lineage to capture everything all in one place.

And just as important. everything should be as automated as possible. If I quickly create a measure, the data lineage should reflect this with no manual input needed (Unless you want to add some description to the new measure as to why it was created)

Create your website with WordPress.com
Get started