Category: Power BI

Reza Rad digs into line charts:

Value Line

Line chart can show one or more measures as measures, such as Sales Amount, Total Costs, Quantity of the goods sold and etc.

Trend Line

Depends on the variety of values across a time period, Line chart can illustrate a straight line as a trend. This trend is good to understand how in overall products are selling, is revenue going up or down for example.

Reference Line

You might want to define minimum, maximum, average, or median values for your line chart as separate lines, and compare values lines with these lines. These are reference lines which can be dynamically created based on value of measures in the chart.

I think this was a rather helpful post in figuring out what Power BI is capable of doing, although if you build a lot of charts that look like Reza’s last example, you probably want to scale that back a bit.

Reza Rad shows how to get the last item in each group using Power Query:

Scenario that I want to solve as an example is this:

FactInternetSales has sales transaction information for each customer, by each product, each order date and some other information. We want to have a grouped table by customer, which has the number of sales transaction by each customer, total sales amount for that customer, the first and the last sales amount for that customer. First and last defined by the first and last order date for the transaction.

In T-SQL, this sounds like the job of window functions.  In Power BI, we write M.

Adam Saxton has a video on how to use Power BI Auditing:

Adding the ability to audit data access is important enough within regulated environments that this was probably a deal-killer until a few weeks ago.

Chris Webb has a workaround for dataset refresh errors when using Power BI and writing M code:

This is just a variation on the widely-used M pattern for using functions to iterate over and combine data from multiple data sources; Matt Masson has a good blog describing this pattern here. In this case I’m doing the following:

• Defining a table using #table() with three rows containing three search terms.
• Defining a function that calls the metadata API. It takes one parameter, a search term, and returns a value indicating whether the search was successful or not from the JSON document returned. What the API actually returns isn’t relevant here, though, just the fact that I’m calling it. Note the highlighted lines in the code above that show how I’m constructing the URL passed to Web.Contents() by simply concatenating the base URL with the string passed in via the custom function’s Term parameter.
• Adding a custom column to the table returned by the first step, and calling the function defined in the second step using the search term given in each row.

This query refreshes with no problems in Power BI Desktop. However, when you publish a report that uses this code to PowerBI.com and try to refresh the dataset, you’ll see that refresh fails and returns a rather unhelpful error message:

Data source error Unable to refresh the model (id=1264553) because it references an unsupported data source.

The nature of the problem makes sense, and Chris provides one method of getting around this error.

Avi Singh shares a few methods of allowing users to personalize their Power BI dashboards:

iv. Row Level Security

Row Level Security proved to be an effective approach for us to provide users a personalized view of their Dashboard & Reports based on the Organization they belonged to. The org hierarchy data was pulled directly from the Human Resource (HR) system, which allowed the Power BI Model to identify which user belonged to which department. In our sample data set, it looks as below.

Read the whole thing.