Month: May 2023

Using ALLEXCEPT To Stop Cumulative Total From Resetting [2023 Update]

Posted on by Annie D

The ALLEXCEPT function is a DAX formula in Power BI that allows you to remove filters from a table or column while keeping other filters on the same table or column. Cumulative totals are a common calculation in Power BI where a running total is displayed over time.

However, when a date selection is made, the cumulative total may reset to zero, and this is where the ALLEXCEPT function can be used to stop this from happening. By using the ALLEXCEPT function, you can ensure that your cumulative totals continue to show accurate data, even when a date selection is Continue reading “Using ALLEXCEPT To Stop Cumulative Total From Resetting [2023 Update]” →

The HEART of Power BI

Posted on by Annie D

Remember Analysis Services? Maybe not for some of you. What exactly powers Power BI? What’s inside that makes the data go? Adam reveals the mystery and takes a trip down memory lane.

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page Continue reading “The HEART of Power BI” →

Calculating Averages Per Transaction Using DAX In Power BI [2023 Update]

Posted on by Annie D

One valuable insight that can be discovered in Power BI is the average amounts per transaction, which can provide useful information for businesses. This insight can be used to compare transaction amounts across different time frames or dimensions in the dataset.

By analyzing this information, businesses can identify patterns and trends, make data-driven decisions, and improve their operations. In this video, Sam will demonstrate how you can calculate averages per transaction in Power BI using DAX.

*****Video Details*****
00:00 Introduction
01:00 Data set
02:12 Average sales per transaction
03:11 Using data bars
03:35 Average profits per transaction
04:30 Average margins per transaction Continue reading “Calculating Averages Per Transaction Using DAX In Power BI [2023 Update]” →

code execution has been interrupted window

How to Make Excel Wait or Pause in a VBA Macro

Posted on by Bb

When running macros, I have often found the need for an Excel wait or pause, mainly so that other things can happen. DoEvents surely helps for waiting for things to happen within Excel, but sometimes you need to wait for processes to start or finish outside of Excel before moving onto other steps in VBA.

Why Wait?

I am a big fan of automating processes, especially with VBA in Excel. For example, I update and save a spreadsheet to SharePoint that a Power BI model is using. I have a Power Automate process set up that automatically refreshes the Power BI model whenever this particular spreadsheet is updated. The challenge is that Power BI temporary locks the workbook as it reads it, so if I am updating a few times in a short amount of time, Excel needs to wait until the SharePoint file is unlocked.

Also, I have another process where I call a Python script and once that is done, I continue on with VBA code to process the workbook Python outputs. I need Excel to wait while the Python code runs. Side note: let me know if you would like me to write more about these other automated processes.

The Solution

Good news – making Excel wait is very easy! Here is the simple code:

This will have Excel wait for 50 seconds since the time is represented as (H:MM:SS). You can adjust as needed.

Bonus

In my code, I don’t need it to wait every time – only when it runs into an issue saving the workbook on SharePoint. Here is how I have set my code using an error handler in my VBA macro.

I hope this helps you! Please comment or reach out if you would like to see other topics covered. I truly enjoy writing about Excel tips and tricks, especially VBA and automation!

Addressed in this post (to help others find this):

  • How to pause Excel
  • How to make Excel pause
  • Make VBA pause
  • Make VBA wait
  • Make Excel wait
  • VBA code wait, VBA macro wait
  • VBA code pause, VBA macro pause
  • VBA wait for another application

RLS in the source? How does this work with DirectQuery and Import in Power BI?

Posted on by Annie D

What happens in Power BI when you have row-level security (RLS) in the source? And, is it different between DirectQuery and Import? Of course it depends! Patrick explains…

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – rel=”nofollow”>https://guyinacube.com/tools/

#PowerBI #GuyInACube

Grouping Bookmarks by Report Page In Power BI [2023 Update]

Posted on by Annie D

Bookmarks in Power BI are useful for saving and restoring specific views or states of a report. They allow users to easily navigate between different sections or pages of a report and can be used to create interactive experiences for viewers.

Grouping bookmarks can improve workflow by organizing related bookmarks together, making it easier to find and apply them as needed. This can save time and help ensure consistency in the report design. In this tutorial, Jarrett shows how to group bookmarks by report page in Power BI.

*****Video Details*****
00:00 Introduction
0:53 Inspiration for the tutorial
02:55 Creating bookmarks
06:33 Grouping the bookmarks

***** Related Continue reading “Grouping Bookmarks by Report Page In Power BI [2023 Update]” →

How to SCHEDULE your notebooks to run with Azure Synapse

Posted on by Annie D

Taking advantage of Notebooks in Azure Synapse Analytics? How do you automate that great work you did? Patrick shows you some options to schedule the run of your notebooks.

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – <a href="https://guyinacube.com/tools/" Continue reading “How to SCHEDULE your notebooks to run with Azure Synapse” →

Calculating Average Monthly Purchases In Power BI Using DAX

Posted on by Annie D

Power BI is a powerful tool that can be used to track expenses and sales for businesses or organizations. It provides a user-friendly interface that allows users to create interactive visualizations and reports using data from various sources.

By using Power BI, businesses can easily monitor their financial performance in real-time, as the tool allows for the tracking of expenses and sales data over time. This data can be used to identify trends, patterns, and areas of improvement, enabling businesses to make data-driven decisions and optimize their financial performance. In this video, Sam shows how you can compare your average monthly Continue reading “Calculating Average Monthly Purchases In Power BI Using DAX” →

How To Transform and Code Addresses In Python

Posted on by Annie D

Transforming and coding addresses using Python involves cleaning and standardizing addresses to prepare them for geocoding. This process typically involves removing unnecessary characters, standardizing street suffixes, and normalizing capitalization and formatting. There are several Python libraries that can assist with these tasks. In this tutorial, Gaelim is going to demonstrate how to transform and code addresses using GeoPy in Python.

*****Video Details*****
00:00 Introduction
00:45 Libraries to import
01:16 Data set
01:39 Initializing the geolocator
03:18 User-defined function
04:45 Converting location to addresses

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE Continue reading “How To Transform and Code Addresses In Python” →

Mac Excel Power Query Three Missing Connector Workarounds – Episode 2597

Posted on by Annie D

Great news: Power Query debuted for the Mac in early 2023.
Bad news: It is missing four important connectors:
• From Table/Range – Fixed in this video
• From Web – Fixed in this video
• From Folder – Fixed in this video
• From PDF – no solution yet.

Thanks to Suat Ozgur – our resident Mac expert at MrExcel, we have solutions for three of those connectors.

Table of Contents
(0:00) Problem Statement
(1:17) From Table or Range
(2:44) How to edit query
(3:33) New icons in Applied Steps
(3:59) From Web
(5:17) From Folder
(9:45) No solution for From PDF yet
(10:27) Thanks to Excel Team
(11:15) Subfolders granted access
(11:24) Avoiding VBA