Tag Archive: Power BI

  • Power BI Drill Through using Multiple Data Points

    A drill through in Power BI allows the reader to see secondary data related to the original page with the context of a specific data point applied, for example, drilling through on sales data can display the demographic information of the relevant customers for those sales. One limitation of the drill through functionality is that it only allows users to drill through on a single data point. If more than one is selected, the drill through…

    » Read more
  • Tabular Cube Processing Report

    I have created a Power BI report which provides detail on the state of processing in a Tabular Cube.The report uses the cube’s dynamic management views to provide information about the cube’s partitions. To use the tabular cube processing report, you need to insert the following information: Server Database (Cube Name) Once entered and assuming the connection is fine you need to accept some native queries. These statements are select…

    » Read more
  • SSAS Tabular Calculation Groups – avoid using SELECTEDMEASURE or ISSELECTEDMEASURE

    Introduction: There is a very serious limitation in the behaviour of calculation groups when using the SELECTEDMEASURE or ISSELECTEDMEASURE functions, and we recommend not using them. Why? If a user creates their own custom calculations within their Power BI report (or composite model) then the value of SELECTEDMEASURE changes, breaking your calculation group logic. Let me explain with an example: In a tabular cube we may look to use calculation…

    » Read more
  • Using ConcatenateX in PowerBI to return multiple values.

    In this blog post we’ll take a quick look at using ConcatenateX function to view a concatenated string of dates where the max daily sales occurred for a given month. I came across this function whilst going through the excellent “Mastering DAX 2nd Edition Video Course” by the guys from SQLBI.com. So credit to Marco and Alberto for sharing this. So how does it work? If we had a list of dates ranging from 01/01/2020 to 31/12/2020 and we…

    » Read more
  • Sorting a Power BI table by multiple columns

    A common request that is raised by clients is how to sort a table in Power BI by multiple columns, in the same way you can in Excel.For a while, there was no way (at least no easy way) to do this until the Power BI March 2020 update. I learnt this tip from the following YouTube video:https://www.youtube.com/watch?v=ik0K1H9j2UcFull credit to Dhruvin Shah, check his video out. Below I have a Power BI table displaying fruit sales, currently…

    » Read more
  • Power BI Databricks Spark connection error

    When querying data from Azure Databricks (spark) into Power BI you may encounter an error: “ODBC:ERROR [HY000] [Microsoft][Hardy] (100) The host and port specified for the connection do not seem to belong to a Spark server. Please check your configuration.“ This is usually caused by trying to connect to a ‘Standard’ Databricks instance, but Power BI (and ODBC in general) can only connect to Databricks using a…

    » Read more
  • Creating calendar tables with DAX using Power BI

    By Nick Edwards In this blog post we’ll take a quick look at creating a self-generating calendar table using DAX. Dates are important if we want to perform time intelligence reporting on our data i.e. yearly sales, monthly sales, weekly sales, year to date sales or previous year sales. We’ll be using the calendar function to create our date table, but there are other methods to do this such as CALENDARAUTO or GENERATESERIES. Here is the…

    » Read more
  • Creating Power BI Layouts using PowerPoint

    First question, why bother with layouts?Using layouts in Power BI allows a user to make their visuals stand out better, the page looks professional and more appealing to its audience. Second question, why PowerPoint?The default page size in Power BI desktop is 16:9, (this trick doesn’t work for other Power BI page sizes), which is identical to a PowerPoint slide.Therefore whatever is designed in PowerPoint will fit onto a Power BI page…

    » Read more
  • Sampling data in Data Lake U-SQL for Power BI

    Being able to hook Power BI directly into Azure Data Lake Storage (ADLS) is a very powerful tool (and it will be even more so when you can link to ADLS files that are in a different Azure account!! – not yet available as at January 2017). However there is a problem, Data Lake is designed to scale to petabytes of data whereas Power BI has a 10GB limit. Yes this is compressed, so we’d expect around 100GB of raw data, however to load…

    » Read more