Power BI

Letting the User Drive – Advanced Techniques for Dynamic Reporting in Power BI

Power BI dynamic reporting is an effective way to consolidate data. In one click, you can view up-to-date statistics or information in real-time, enabling users to connect with data through interactive features and other functionalities to perform simple and advanced data analysis.

 

In this recent webinar, presented by Lead Consultant Robin Abramson, we looked at the approach of dynamic columns, some new methods, such as line and bar charts that appear or disappear depending on slicer choices, and a trend of efficient space usage to encourage analysis.

 

Additional topics covered:

  • How to craft reports that feel natural to the pickiest users
  • Techniques that mimic features of other reporting tools
  • Creating date slicers that go beyond what’s natively allowed
  • How to create app-like experiences that become corporate favorites

 

If you are looking to learn advanced techniques for dynamic reporting in Power BI or how to produce an  “app-like” report that will give your users a sense of shaping their own experience, then this webinar is right for you. You can watch the complete webinar below.

 

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected]

3CloudLetting the User Drive – Advanced Techniques for Dynamic Reporting in Power BI
Read More

Power Platform – Power Apps, Power Automate and Power BI Integration Demonstration

Microsoft’s Power Platform helps organizations develop solutions, evaluate data and automate processes to enhance and innovate their operations. When combined, various applications allow businesses to build effective end-to-end business models that connect with hundreds of cloud-based and locally operated apps and services.

Together, Power Apps and Power Automate are often discussed, with Power BI kept separate for application reporting. In this demo-heavy webinar, the 3Cloud App Modernization Team gives you an overview of all three products integrated into a single sample approach to help you reduce the data errors in Power BI reports.

This webinar covers:

  • Power Apps, which provides management of a product catalog & unit prices during the demo
  • Management of Row Level Security (RLS) data hosted in a Dataverse repository
  • Power Automate approval workflow
  • Generation of an RLS Audit report PDF via Power Automate
  • Bi-directional capabilities of a Power Apps embedded into a Power BI report

If you are looking to see a working example of these technologies streamlined into one solution- specifically with Power Apps embedded into Power BI, then this webinar is for you. You can watch the complete webinar below.

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudPower Platform – Power Apps, Power Automate and Power BI Integration Demonstration
Read More

Daylight Savings Time Changes in Power Query

In this post, I’ll show how to accommodate for Daylight Savings Time changes in a Power BI file. The video I’ve included will show the formulas that I use and how they adapted from Daylight Savings Time to Standard Time. There are many ways to do this and it is best to find a way that accommodates what you need to do, but I’ll walk through how I do it for my needs within Power Query.

  • The first thing I do in Power Query is to identify the start and end date of Daylight Savings Time. In the US, the start date is the second Sunday in March and end date was the first Sunday in November and I’ve added the time of 2:00am.
  • You could look these up and make a list of them for the next 8 or 10 years but to do it formulaically, I use a DateTime.LocalNow and Date.Year to pull the year out of whatever year we are in.
  • In the Applied Steps area in my query settings, I create a small temporary calendar table using that the year that will start in March and end in November. I create a list of those dates under source and then I convert that list into the actual dates.
  • No I have a calendar table with column1 being all dates listed from the start of March thru November. Using the Added Custom fields in Applied Steps, I add a column for month number and one for number of the day of the week (0-6).
  • Since I’m only interested in Sundays in March and November, I filter this table to show me only Sundays (day of week 0) in those months. More specifically, I need to know the second Sunday in March and the first Sunday in November. To do that, I want to group rows by their month number and then add an index column.
  • If I click on one of the columns in my table, I can see a Sunday index which is counting in order (1st, 2nd, 3rd Sunday). If I remove the other columns and just keep the one with the index and expand it, I see I have all the information pieces I need (month number, Sunday index columns).
  • Next, I’ll add a little helper column (GetDays) that will combine the month number and Sunday index columns together. I filter that helper column for the 2nd Sunday in March and the 1st Sunday in November.
  • So, I have all I need but I just need to add a time, so I’ll put a time component in and combine it with the date, so I have a DateTime column.
  • It’s important to note that when we do comparisons in Power Query between dates, date times or date time zone, you want to be consistent with your format. If I’m going to compare date time zones, I need to put my date times into a date time zone. I also need to ensure that the time zone is the same, so I’m going to use a consistent switch. I’ll add a time zone to my start and end dates.
  • I’ve added two queries (StartTime and EndTime) in which I’ll reference that DateTime list. In my formulas, I’ll write in the DateTime in March as the min and the end DateTime in November as the max. I’ll also need to add the time zone to each and make sure I’m consistent. Check out my video for more detail of the formulas.
  • The logic here is an If/Then statement. If the switch time zone which I’ve set to -4 (which is Eastern Time during Daylight Savings), is greater than the start time and less than the end time, then we are in Daylight Savings and the time is correct.
  • If it’s not in between those two dates, then we’re not in Daylight Savings and we need to subtract an hour from it because now we’re going to fall back. In other words, we’re going to be 5 hours behind instead of four. If I refresh it again, it should match the time that’s on my computer. In my SwitchTime Zone column, I see the time that it was in the summer and in my DateTime with DST Correction column I see the correct time now which matches my computer, so I know that this is working correctly.
  • But how do I know it’s working to the exact second? I’ll just duplicate my three queries and create a testing query. In this testing query I will arbitrarily set the start and end time to be a specific day and time to check back on my table to see if it matches. This testing helps ensure that it’s working perfectly.

My video below goes into more detail and may make this written demo clearer. I hope my peek into how I can accommodate for Daylight Savings within Power Query is helpful. This is certainly not the only way to do this, but it works well for me, so I thought it was worth sharing.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected]

Steve WiseDaylight Savings Time Changes in Power Query
Read More

Tabular Modeling & Calculation Techniques Beyond the Basics in Power BI

Do you want to learn how to address real-world issues with Power BI and DAX? In a recent webinar, Principal Consultant, Paul Turley, teaches you what you need to know. Paul covers many-to-many relationships, using disconnected tables and takes a look at using composite models.

This presentation takes a deep dive into Tabular modeling and calculation and includes demos. Paul’s agenda covers:

  • Dimensional modeling basics (just a quick run through as this is a more advanced presentation)
  • Filter propagation in Tabular models
  • Effects of single and bi-directional relationships as you are creating relationships in your data model
  • A look at scenarios where data model relationships can or cannot be used to achieve requirements or may or may not solve business problems
  • Dynamic measures built on a dimensional data model
  • A demo on DirectQuery in a Gen 1 composite model with Import mode
  • A first look at new Gen 2 DirectQuery using Power BI datasets which is the next generation of composite model capabilities which is now in public preview

All these topics and demos are based on a business requirement or business problem context, so the presenter will set up a business problem and work through how it can be solved with dimensional modeling.

So, if you’re looking to learn about using Power BI and DAX to address real-world business issues, then this webinar is for you. You can watch the complete webinar below.

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudTabular Modeling & Calculation Techniques Beyond the Basics in Power BI
Read More

A Few Tips When Creating Power BI Mobile Reports

Power BI is the best BI and reporting tool out there, and so is Power BI Mobile. Microsoft has made mobile access a first-class experience in the Power BI platform. All the heavy lifting like authentication, data security and network connectivity is all automatically delivered out of the box.

Just download the app and sign in once, then sit back and enjoy access to all your reports and dashboards. You’ll also get integration with your mobile device’s virtual assistant, so you can ask Siri for the sales report and she’ll pull it up for you to view.

Awesome stuff, right? But as a developer, you may not know how to deliver an optimized view of your analysis for mobile consumption. Here, I’ll walk you through creating a mobile version of your report. I’ll point out the major features, as well as some valuable tips and tricks.

  • In the Power BI Desktop view, you can look at the design for the mobile view by clicking on View and then Mobile Layout. This gives me a blank canvas and on the right side is a visualizations pallet, which at this point only lists the visualizations from my desktop.
  • I can pull over one of my desktop visuals and add my report title and company logo, but honestly, it’s not that great. I want to change the way it looks or even create a totally different visual just for my mobile layout, but unfortunately, I’m stuck with the visuals that I have on my desktop.
  • To do this, here’s my first big tip for a work around; you’ll need to create visuals, put them on your desktop version, and then hide them. Let’s walk through this:
    • The title bar ‘Sales Analysis’ on my mobile view is huge so I want to make it smaller. I go back to my desktop and make a copy of my title bar and make one with a smaller font size.
    • Next, I go over to my mobile layout, get rid of the large one and add the smaller one.
    • Here’s the problem, when I go back to the desktop, I want to get rid of that smaller font title text box and when I delete it, it will disappear from the mobile view as well.
    • If I try to hide it in the desktop view, it will also hide it in my mobile view.
    • So, my trick is to hide that text box with the smaller font behind a larger text box in my desktop view. All I need to do is lineup that mobile only text box on top of the other one, go to Format and click Send to the Back. You can also do this in the Selection panel on the right side and drag the text box to the bottom of the list, and it will put it behind everything else higher on the list.
  • Next, I want to create a visual that is more meaningful in my mobile view. In my demo, I create a copy of my bar chart visual and I filter it to show only the most recent 3 months of my data.
  • I’ll shrink that down a bit and hide it behind the larger bar chart on my desktop. That new 3-month bar chart will appear in my mobile visualizations pallet and I can pull that over onto the canvas.
  • When I save this and publish it, I’ve created one version that users who view it from the desktop will get the desktop view. Users that view it from their phone will see the desktop view if they are in horizontal mode but will get the mobile view if they are in vertical mode.

When I’m on my phone and open the Power BI app, I see two different icons. The mobile version I created and published has a different icon with a phone on it. This way end users can quickly identify the version that is intended for the mobile use. I can rotate my phone horizontally and see the desktop version, but the goal of creating a mobile view of reports is to just have distinct data (in my case, the most recent 3 months) available for a quick update.

I can also add a quick Siri shortcut by simply telling Siri that when I say, ‘open mobile sales report’, my phone will go right to that mobile report. These tips should be super helpful when creating Power BI mobile reports.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Chris SilvernailA Few Tips When Creating Power BI Mobile Reports
Read More

Avoiding Issues When Generating Time/Date in Power BI

Have you ever run into hurdles when trying to generate time and date in Power BI? I know I have, so I’m here to help and to demonstrate the differences between generating time and date in Power BI Desktop vs in the Power BI Service.

A few key points before I dig in:

When we generate time and date in Power BI Desktop using the native Power BI tools, it pulls that information from our local computer settings.

In the Power BI Service, the Service generally exists in a different server time and date than your local machine. Therefore, there will be some differences between the times as they appear in the desktop file and how they appear in the Service.

Currently, there is no native functionality to handle Daylight Savings Time changes. Keep this in mind when you start to generate your own time and dates – you’ll need to create tools that take this into account.

What I’ve done will be much easier to see by watching my video instead of trying to explain in text, as it could be confusing. Here’s a brief overview of my video.

  • In my video, you’ll see that I went into Power Query and copied the different queries that I used to generate time and date.
  • The first one is generating time and date in Power Query using DateTImeZone.LocalNow to pull the local time. Remember, when operating in the PBI Desktop, this is pulling information from my local machine.
  • What I’m going to do is reuse some of the formulas and look at how we can switch the time and what those changes look like as I go through the queries I’ve highlighted.
  • In my demo, you’ll see that rather than publishing this file and jumping in the Power BI Service to look at it, I’ve taken the queries that I’ve used to generate my times, put them in the Service as a data flow, and then imported that dataflow into a Power BI Desktop file.
  • So, I have 2 pages; one shows the information from the data flow and the other is the information generated locally. This way I can show side by side what the changes are going to look like.
  • I’ll walk you through the steps of these scenarios and the queries I used:
    • Manually switching time using #duration
    • Modifying date/time to reflect Eastern Standard Time (-4 UTC) using SwitchZone
    • Modifying time/date in Power Query using SwitchZone and FixedLocalNow (time at start of query execution)
    • Adjusting time -4 or -5 based on daylight saving calendar

In summary, this post covers the changes to date/time as we move from Power BI Desktop to the Service and what are the best formulas to use when we are generating our own time. You’ll see that the key takeaway is the best way is to use SwitchZone when adjusting between the Desktop and the Service.

I think you’ll find this helpful when running into date/time hurdles in Power BI.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Steve WiseAvoiding Issues When Generating Time/Date in Power BI
Read More

Getting Started with Custom Power BI Report Themes

Do you want to learn how to create visual consistency across your reports and eliminate the monotonous task of setting common visual properties? Power BI report themes are more than just a color palette for your reports. With report themes, you can apply design changes to your entire report. All the visuals in your report will use the colors and formatting of the theme you’ve selected as a default. You can make design changes like using corporate colors, changing icon sets or applying new default visual formatting.

In a recent webinar, 3Cloud Senior Consultant, Jeremy Black, takes you on a deep dive into Power BI report themes. Jeremy covers the two types of report themes: built-in and custom. With custom themes you can customize via Power BI Desktop and/or by manually manipulating the JSON theme file used by Power BI Desktop. He’ll also cover the benefits of creating custom report themes, as well as their limitations.

Lastly, the presentation will delve into the Power BI report theme building blocks including data and structural colors, text, and visual styles. Most of this webinar is spent on demos of examples of how to begin customizing your report themes.

You can watch the complete webinar below.


So, if you’re looking to create visual consistency in your reports without spending tons of time setting common visual properties for your reports, this webinar is for you. Join us for our free weekly webinars covering a variety of Azure topics every Tuesday at 11 a.m. ET. Check out our events calendar for upcoming topics.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudGetting Started with Custom Power BI Report Themes
Read More

Setting Variables Using DAX in Power BI

Welcome to another Azure Every Day focused on one of my favorite topics, using DAX in Power BI. In a previous post, I covered a couple of my favorite DAX formulas, CALCULATE and FILTER. Here, I’ll discuss variables and how to work those into CALCULATE/FILTER to expand on that and make it even more powerful. You may want to check out my previous post here, as I’ll be continuing using that demo.

  • I started by building in a CALCULATE/FILTER function in a table to calculate the beginning balance for 2017 for all my assets.
  • My code (see my video demo for code detail) tells it to calculate the sum of the beginning balance and I filtered the table where fiscal year equals 2017, and finance type equals assets.
  • Now, let’s say we want to know the assets for every year, not just 2017. To do this, we need to set the year into a variable and then it will calculate that asset for each individual year.
  • You use the VAR function to set the variable in the code and you’ll need to give it a name. In my case I’ll use YR for year and I’ll have that EQUAL to the fiscal year. It’s important to note that anytime you set a variable, you must hit return at the end.
  • Next, I’m going to update my FILTER. My code is calculating the sum of the beginning balance and I’m filtering the table where fiscal year equals 2017, but now I want to take that out and change it to fiscal year equals year.
  • How this works is when this goes through each row it will calculate for each year by using the variable instead of hard coding the year into there.
  • So previously we only had one outcome for 2017, now when we submit this, we’ll see four outcomes as we have four years’ worth of data, so we’re getting a calculation for our beginning balance for each year.
  • We can even step this up a bit if we wanted not only the beginning balance for each year, but also for each finance type. Maybe we don’t just want the assets but other values like equity, expense or liability.
  • All we need to do is set another variable that I’ll call FT for finance type. And instead of doing this for where finance type equals asset, we’ll say where finance type equals our variable.
  • Now we’ll have the calculation for every year for every individual finance type.


 

I hope this quick example helps you to start using these DAX formulas in your reports. The CALCULATE/FILTER functions and using variables are something I use all the time in Power BI.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Alex BeechSetting Variables Using DAX in Power BI
Read More

DAX CALCULATE and FILTER Functions in Power BI

We all know Power BI is a powerful analytics tool and it’s easy to create a new Power BI Desktop file, import some data into it and create reports that show valuable insight. Adding Data Analysis Expression, better known as DAX, enables you to get the most out of your data.
If you’re new to Power BI or DAX, DAX is a collection of formulas, or expressions, that are used to calculate and return one or more values. DAX helps you to use data already in your model and create new information from it that can solve real business problems.
When I first started using DAX functions, it brought my Power BI skills to the next level. I was able to tackle some analytical needs that I had struggled with in the past. I’m here to share a couple favorite formulas that I use all the time called the CALCULATE function and the FILTER function. Please be sure to watch my video included in this post as I walk through using this DAX formula.
• In my demo, I’m working with a data set to find the beginning balance for 2017 for our assets.
• To do that I need to sum a column in my table called beginning balance when fiscal year equals 2017 and when financial type equals asset.
• I’ll do this by using a combination of the CALCULATE function and the FILTER function. The CALCULATE function allows you to calculate a function on the entire table.
• In my code I’m going to CALCULATE the sum on our beginning balance. This would calculate the sum for the entire table.
• But we only want to calculate the sum for 2017 for just the assets and financial type. For this, once we have calculated the table, we need filter that table. Think of this FILTER function as making a digital table in the background.
• We need to FILTER it where fiscal year equals 2017 and where finance type equals asset. In my code, I’ll add FILTER for the function, and we need to tell it what table we are going to be filtering, in my case it’s the balance table. Then add where fiscal year equals 2017 and where finance type equals asset.
• Using these DAX functions, our result will show the beginning balance for our assets for 2017.
• My video shows you exactly how to write the code I used here, so be sure to check it out.

As you can see, this is super simple, and this formula allows you flexibility in how you write it. You can FILTER tables in many ways and use different functions within CALULATE. I hope you enjoyed this simple use case of these powerful DAX functions in Power BI.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected]

Alex BeechDAX CALCULATE and FILTER Functions in Power BI
Read More

Create a Custom Visual in Power BI with React

Welcome to another edition of Azure Every Day! I’m an App Development Consultant at 3Cloud and in this post I’ll show you how to create a custom visual in Power BI with React.
When creating a custom visual in Power BI, you use the TypeScript language. You’ll be fine with TypeScript if you are familiar with JavaScript. Once you’ve created a custom visual from scratch, you can test and debug your visuals online using live reports, as well as package and deploy your visual to a production report. You can check out the Microsoft documentation about custom visuals at https://powerbi.microsoft.com/en-us/developers/custom-visualization/ to learn more.
I’ll walk you through the process here, but also be sure to watch my video included at the end of this post.
• To get started, be sure you have node installed on your machine.
• Navigate to your project directory run the npm install command for Power BI visual tools. Once Power BI visual tools are installed, run pbiviz, new and then your project name.
• Next, run pbiviz start which will start your custom visualization in the local host.
• To debug your visualization, go to app.powerbi.com and create a new report (or use an existing one) and add the developer visual to the report.
• In order to add React elements to your custom visual, you’ll need to install React and ReactDOM. Import React and ReactDOM into the visual.ts file or whatever file you will be using to render the HTML elements to the DOM.
• Create a React element within the visual.ts. Also create the DOM element and add an element to the DOM that is referenced by this element.
• Next, create the React component using React.createElement of the component name that you’ve imported, then pass any props you would like in the second parameter of the React.createElement function.
• Finally, you can add the React element to the DOM by using ReactDOM.render and reference the React component that you built and the HTML element on the page that you would like to add the React element to.
• When you’re finished building and debugging your project, you can set the project and author details in the pbiviz.json file.
• Run the pbiviz package command to generate a pbiviz file to import into your Power BI report.
• Now you can go into your report and import a visual from file. Select the visual and add the data you would like to add to your visual and configure the settings you had previously.
• Please check out my video for code detail on all the above steps.

 

This post walked you through the steps to create a custom visual in Power BI using React. I hope you’ll give it a try.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected]solutions.com.

Tom WardCreate a Custom Visual in Power BI with React
Read More