On the podcast, Pfeffer gave a couple of people as examples: one was a tall woman, who also wore heels. The other was a short man, who commanded a room as if he were much taller. While both of these examples are height related, they also emphasize leveraging your strengths is a persuasive way. They also connect with another rule, which is to create a powerful brand. It seems to me that in-person will always be more powerful than remote, but even so, there are things I do to show up in a powerful fashion in a remote workplace.
Our family just started using Kanban in the house. Kanban is a project management tool useful for prioritizing tasks as they come up. It’s typically used in work situations like maintenance, where emerging issues might pop up. I’ve been wanting to try Kanban for our household, and finally got it done. …
TL;DR: Power BI has evolved into a complicated feature-rich product. Fabric looks like an attempt to fold some of that complexity into the background, while including more comprehensive governance.
In the section of Azure Power BI D5-500 training, I was surprised (and delighted) to see that Slowly Changing Dimension (SCD) Type 2 is presumed as the format for dimensions— and that these historical dimensions were connected directly to the fact tables. Up to this point, most data warehouses I have run into have used SCD Type 1 as the default, and then required analysts to do gymnastics to connect to a historical dimension. [I also include a link for what to do if this is not the design of your data warehouse]
Simplicity is key to increasing usability. I want to enable report consumers to explore the data without having to think about the process of exploration. With this solution, a consumer can move back in time one quarter in time or jump from the current quarter to to the same quarter last year while keeping as much the same as possible, making it easier to think about comparisons.
This error, “couldn’t retrieve the data for this visual”, can be a frustrating one. Like other tile errors, it only shows as a broken tile with the message “can’t display the visual” until you click to see details. If we consult the official Power BI documentation on tile errors, the documentation currently says: “This issue is usually transient. If you try again later and still see this message, contact support.” I’ve seen the error in three situations: the most common is that a report consumer lacks build access to a report built on a DirectQuery over Power BI dataset connection.
I decided to share my tips for learning DAX. 1. Learn about filter context; 2. use variables to debug measures, and 3. understand how CALCULATE works. At the end, I link two of my blog posts: one which applies the concepts of coordinates and values to the different visualizations available in Power; the other is how to troubleshoot the trickiest visual of them all: the table visual.
On my About page, I claim that “I studied English Literature because it’s fun to find patterns in the unstructured data of a 1,000-page novel.” Nobody ever challenges me on this statement, so I thought I would give an example from my college years. I first read the short story, “Just Like a Tree,” when I was in community college.
Date table doldrums: I love getting date tables from data warehouses. I don’t love manually setting data types, renaming columns, and turning off default summarization for these columns. In this post, I use the ribbon and buttons for doing most of these things, and I use some custom M when needed to speed things up.
I have organized my blog into two categories: tech and life. You can subscribe by RSS to one or the other, to both, or to none. There are also navigation pages for readers to read one, the other, or all.
Quick tip for pinning data sources in Power BI Desktop. Useful for SQL, Oracle, Snowflake, SharePoint, and other data sources instead of copy pasting every time.
You can easily start using table visuals without having to know how they work under the hood. When you run into a problem, that’s when knowing about the mechanics becomes helpful.
Values & Coordinates. I think of values as dynamic and coordinates as static. If values are the amounts and the counts, then coordinates are the categories, attributes, and time units, which break up that value for comparison. In a Power BI data model, values typically come from transaction tables or fact tables, while coordinates typically come from lookup tables or dimensions.
“Can’t determine relationships”: bi-directional filters. Most of the time this error means that a visual has two columns from different tables with no relationship between them. But, if can also happen when using bi-directional filters. The tables are related, but Power BI can’t figure out how to manage the cross-filtering.
On the podcast, Pfeffer gave a couple of people as examples: one was a tall woman, who also wore heels. The other was a short man, who commanded a room as if he were much taller. While both of these examples are height related, they also emphasize leveraging your strengths is a persuasive way. They also connect with another rule, which is to create a powerful brand. It seems to me that in-person will always be more powerful than remote, but even so, there are things I do to show up in a powerful fashion in a remote workplace.
Problem: ‘invalid identifier’ error when connecting to columns which are named using mixed case in Snowflake database.
Solution: in the Advanced tab of the connection, turn on Quoting Identifiers: Double Quotes (“ “).
Our family just started using Kanban in the house. Kanban is a project management tool useful for prioritizing tasks as they come up. It’s typically used in work situations like maintenance, where emerging issues might pop up. I’ve been wanting to try Kanban for our household, and finally got it done. …
TL;DR: Power BI has evolved into a complicated feature-rich product. Fabric looks like an attempt to fold some of that complexity into the background, while including more comprehensive governance.
In the section of Azure Power BI D5-500 training, I was surprised (and delighted) to see that Slowly Changing Dimension (SCD) Type 2 is presumed as the format for dimensions— and that these historical dimensions were connected directly to the fact tables. Up to this point, most data warehouses I have run into have used SCD Type 1 as the default, and then required analysts to do gymnastics to connect to a historical dimension. [I also include a link for what to do if this is not the design of your data warehouse]
Simplicity is key to increasing usability. I want to enable report consumers to explore the data without having to think about the process of exploration. With this solution, a consumer can move back in time one quarter in time or jump from the current quarter to to the same quarter last year while keeping as much the same as possible, making it easier to think about comparisons.
This error, “couldn’t retrieve the data for this visual”, can be a frustrating one. Like other tile errors, it only shows as a broken tile with the message “can’t display the visual” until you click to see details. If we consult the official Power BI documentation on tile errors, the documentation currently says: “This issue is usually transient. If you try again later and still see this message, contact support.” I’ve seen the error in three situations: the most common is that a report consumer lacks build access to a report built on a DirectQuery over Power BI dataset connection.
I decided to share my tips for learning DAX. 1. Learn about filter context; 2. use variables to debug measures, and 3. understand how CALCULATE works. At the end, I link two of my blog posts: one which applies the concepts of coordinates and values to the different visualizations available in Power; the other is how to troubleshoot the trickiest visual of them all: the table visual.
On my About page, I claim that “I studied English Literature because it’s fun to find patterns in the unstructured data of a 1,000-page novel.” Nobody ever challenges me on this statement, so I thought I would give an example from my college years. I first read the short story, “Just Like a Tree,” when I was in community college.
Date table doldrums: I love getting date tables from data warehouses. I don’t love manually setting data types, renaming columns, and turning off default summarization for these columns. In this post, I use the ribbon and buttons for doing most of these things, and I use some custom M when needed to speed things up.
Filter by List in Power BI Desktop instead of merge to keep query folding on. Step-by-step instructions. Contains link to sample file.
build a robust data model in Power BI using a data warehouse presentation outline
Some details about how to use stand-alone and inline variables in M Power Query queries.
My guest post blogs on Power BI at P3 Adaptive. Data warehouse, data governance, and hacks!
I have organized my blog into two categories: tech and life. You can subscribe by RSS to one or the other, to both, or to none. There are also navigation pages for readers to read one, the other, or all.
As a first step in moving from traditional BI to Power BI, build a data mart using Power BI dataflows.
Quick tip for pinning data sources in Power BI Desktop. Useful for SQL, Oracle, Snowflake, SharePoint, and other data sources instead of copy pasting every time.
Power BI. Get data from web, multiple columns. Obituaries.
Tool to check row-level security in Power BI Desktop files or Power BI Premium
Tool to compare relationships between two models. 2 .pbix files, 2 models in premium workspaces. Or 1 local and one in premium.
You can easily start using table visuals without having to know how they work under the hood. When you run into a problem, that’s when knowing about the mechanics becomes helpful.
Values & Coordinates. I think of values as dynamic and coordinates as static. If values are the amounts and the counts, then coordinates are the categories, attributes, and time units, which break up that value for comparison. In a Power BI data model, values typically come from transaction tables or fact tables, while coordinates typically come from lookup tables or dimensions.
“Can’t determine relationships”: bi-directional filters. Most of the time this error means that a visual has two columns from different tables with no relationship between them. But, if can also happen when using bi-directional filters. The tables are related, but Power BI can’t figure out how to manage the cross-filtering.
New blog. Power BI, Direct Query, DAX, and more.