A Fine Slice Of SQL Server
Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use January first to December 31. So we get the entire year, is that what we want? Or do we want to see the sales for the previous year until the day we have data for this year so we can compare? Both need different DAX so let’s take a look.
Read on for a detailed analysis, including where you might go wrong.
Comments closedMatt Allington solves a problem in Power Query:
Suppose you have data in the form of dates (not consecutive) with a value for each of the dates (see the table below left side). You need to expand the rows of the table (create the missing rows) so that you will have all the consecutive dates in the given range and each of the dates has the previous updated value (see the table below right side).
The solution has a pretty large number of steps but is straightforward.
Comments closedImke Feldmann takes us through a workaround for an interesting error:
When running R-scripts in Power BI, I got all sorts of error-messages who all had one thing in common: They were complaining about one or more packages being installed by an R version with different internals.
Click through for the solution. I’m not sure I’ve run into this issue before and I’d rather keep it that way.
Comments closedA fascinating insight into how Power BI works, but is this any practical use to us? Let me be clear: I don’t think you should be using any of these functions yourself in a real-world report. I’m sure all this would be documented and publicised if Microsoft did want us to use it ourselves! Another consideration is that these new functions return tables and that makes them awkward to use in regular .pbix Power BI reports – I guess we could create calculated tables although that’s not as flexible as returning a table from a query as shown above. That said, even though we can’t write our own DAX queries in regular Power BI reports, we can write our own DAX queries in Paginated Reports and we can now create Paginated Reports that use a Power BI dataset as a data source. I tested putting one of the queries generated by the Key Influencers visual into a Paginated Report connected to the same dataset and it worked ok (even after publishing). You can also embed DAX queries connected to a published dataset in Excel too, as I show here. Hmm, plenty to think about then…
Chalk this up as “fun to know but not recommended to use yourself.”
Comments closedPrathy Kamasani shares some thoughts on what makes for a good Power BI mobile report:
Steadily more users are getting interested in Power BI mobile reports, especially when you design reports for executives; they are more interested in mobile view, of course, they do want to explore the detail data but not always. Until recently, I was someone who doesn’t pay much attention to mobile reporting unless the user explicitly asks for one. So, when I don’t create a layout for mobile when the user opens the report using Mobile App, it automatically opens the report in landscape mode. Which is fine but it’s not designed for that purpose, it misses the sleek look.
Creating a mobile report is pretty easy in Power BI but creating a good one is not that easy. In this blog post, I want to show how I approach mobile reports in Power BI.
For me, the key is glanceability. People looking at a mobile report typically don’t have fine-grained control mechanisms like a mouse—it’s thumbs on a phone screen (or maybe fingers on a tablet if you’re lucky). This means you don’t want fine-grained controls. Instead, lay things out as simply as possible and don’t make people click on tiny sections of the screen.
Comments closedImke Feldmann has an interesting solution to a text search problem in Power Query:
In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:
Table.Contains( YourTableName, [Description = "blue"] )But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.
Read on to see how to do this.
Comments closedDustin Ryan gives us a Power BI dashboard covering NFL draft history:
With the 2019 NFL Draft upon us, of course I wanted to visualize some NFL draft data in Power BI. So I put together this interesting set of visualizations based on some data I scraped from Pro Football Reference. The dataset includes drafts from 1936 to 2019 including picks through round 6 where applicable. I’ll update the dataset as the remaining rounds of the 2019 draft are completed. So feel free to take a look, interact with the dashboards, and let me know if you have any questions.
Click through for the dashboard.
Comments closedErik Svensen shows how you can migrate a Power BI data model up to Azure Analysis Services:
After the Azure Analysis Services web designer was discontinued per march 1 2019 – link – there is no official tool to do a move of a PBIX datamodel to Azure Analysis Service. But by using a few different tools we do have ways of doing it anyway.
Click through for the step-by-step instructions.
Comments closedIf you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.
Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already
Read on to see how.
Comments closedI have written a few articles in the past that toy with the ideas of changing visibility and text colour based on selection. I started to wonder if it was possible to make a visual appear (or not) based on a selection from the user. There is no out of the box way to do that today. It is possible to use bookmarks to show an hide an object, but the user must click a specific button to do this. I want the user to be able to interact with a report and see (or not see) a chart based on some valid selection across the report. Microsoft is already working on building expression based formatting across the breadth of Power BI however as of now the only item you can change is the header in a chart.
Hopefully this gets better over time.
Comments closed