3- Click on Load. But all calculated dax columns will go away. You can choose ‘Model View’ in the left pane, select the corresponding table using ‘DirectQuery’ storage mode, go to ‘Properties’, change the storage mode from ‘DirectQuery’ to ‘Import’. With no data connections, Power Query Editor appears as a blank pane, ready for data. The largest Power BI and Fabric virtual conference. Data from the table is being loaded into memory, but at the query time, it can be also retrieved directly from the source. It describes DirectQuery use cases, limitations, and guidance. Any easy way to do this (aprt form. In the Advanced Editor, locate the "Source" step and change the. I have a report that pulls data from 11 mil rows and 10 mil rows SAP HANA views in direct query mode. Reports created using Power BI Desktop can then be published to the Power BI service. Below is a list of changes that can change the storage mode of the table from DirectQuery to Import. I've read that with "DirectQuery" mode, that Power BI will query the data source to get data for the report (unlike with import. Click on “Add a Web Part” to add script editor web part. In this article, we explored DirectQuery mode for the Azure Analysis Service database in the PBD. I am trying to make a new calculated column or measure (whichever is compatible) in my report. Best Regards, Allan. Data from the table is being loaded into memory, but at the query time, it can be also retrieved directly from the source. you only then get to decide if you want import or Direct Query. DirectQuery will give you a few Power Query options. Power BI Direct Query is a method for retrieving data directly from its source, at the time of the query. I want to getting data from sql IThe 'Import' option is worked correctly. After clicking on this, a different popup appears in the desktop window saying "Please apply or discard the pending query changes and then try again. What can be done in this situation?Approach 2: Direct query to Schedule. I am trying to make a new calculated column or measure (whichever is compatible) in my report. Once in that group, select the option for time and you’ll see from the dropdown a choice for local time. More information: When to use Import or Direct Query mode Select OK to continue. While it's possible to switch a model from DirectQuery mode to use import mode, all the necessary data must be imported. Here is an idea. powerbi. Then. But if I take the same query and convert it to a. Read more. One issue I have is that, during tournaments, the scores in the OneDrive Excel file (which the Power BI Report uses as an import data source) are frequently updated. You can then import the file into Power BI where a semantic model exists. Hello @akiko_lim,. One has to make a new table in PowerQuery. Merge query as new and display expected result as above. Currently, for Power BI December 2020, If the data source is Power BI datasets or Azure Analysis Service, you can change the connection mode from LiveConnection to DirectQuery. Hold the Shift key and click on the bottom query — this will select all queries. Query parallelization is enabled on Power BI Premium and Embedded SKUs with eight or more vCores. You can't change from import to direct query, more details as the MS Docs site The site states: Changing the Storage mode of a table to Import is an irreversible operation. Change the extension for the first Power BI Desktop file with the imported data model to . You should have 0 queries in the list. By my test in the. Using DirectQuery with Power BI dataflows lets you connect directly to a dataflow without the need to import the data into a semantic model. As you move from Import to Direct Query to Live Connection, you trade off ease of use for solutions that will scale. Power Apps. IF Statement, Switch statement - Direct Query 07-28-2020 02:01 PM. Open advanced editor for the new query and paste the code you copied earlier. Data can be imported or data can be queried directly using DirectQuery, or a live connection to SQL Server Analysis Services. Microsoft Fabric can ensure business users in your organization have access to the data and insights needed to make data-driven decisions. 03-06-2023 10:05 AM. I understand that Power BI Desktop currently provides limited functionality when using DirectQuery, but I'm having trouble establishing if I've bumped into the limit of its capabilities, or my own 🙂. reports dataset. powerbi. Each query is limited to return less than or equal to 1 million rows. Use your current credentials instead. I do not need the historical database to be an direct query but do need the other currently updating query to be a direct query as this one is still being updated daily. Subscribe to RSS Feed; Mark Topic as New; Mark Topic as Read; Float this Topic for Current User; Bookmark; Subscribe; Printer Friendly Page; All forum topics;. This article discusses troubleshooting. 08-01-2019 07:07 AM. This works with a direct query as well. 2. A DirectQuery source can be either a relational database or another tabular model, which can be a Power BI dataset or an Analysis Services tabular model. When connecting to data in Power BI, you need to choose whether you want to use Import or Direct Query mode for connecting to your data. However, just connect the table using direct query then copy the m-code form the advance editor and replace the existing table code which is connected through import mode. Ideas (powerbi. If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 5- click on Switch. Ways to work around DirectQuery in Power BI. Click on Projects to Open Project Center Page. "Power BI relies on the following to create and manage automatic aggregations. Power BI tracks model and user report queries in a query log. I am actually using this dinstiction in a google chrome extension I am writing to display Power BI Reports (now BOTH Import and Direct Query reports) as a real-time, updating slideshow. Here are the steps you need to take: Open your Power BI model and select the report containing the dataset that you want to switch to import mode. Approach 3: Use partitioning concept to refresh dataset partially. pbix Power BI Desktop file that does either or both of the following actions: Combines data from one or more DirectQuery sources. Power BI Report Server. Message 13 of 14. @KasperJ90 So, I would highly recommend creating a SQL view that merges the 3 views and then you would remove your import query and create a new query named the same thing that connects to this view in DirectQuery mode. If you Premium Per User or Premium licensing, can you just setup a refresh pipeline through Power Automate or Powershell? That way, you won't have any refresh limits (you can go past 48 refreshes/day), and it will get Import Mode datasets to refresh at high frequencies. Direct Query to Import, it works. i. Super User. Use Import mode when: Your dataset is small. If you click transform data while still in direct query mode you can apply filters in Power Query to limit the data. 581 64-bit (November, 2017)The auto date/time is a data load option in Power BI Desktop. Whereas Import mode stores a snapshot of the data in memory, Power BI Direct Query (DQ) does not store any data. Click on the "Edit Queries" button in the "Home" tab to open the "Power Query Editor". Direct Query, on the other hand, leaves the data at the source and queries it on the fly as needed. Save your report, and then republish it to Power BI service. Microsoft 365, SharePoint Online, Microsoft Teams, Viva, and Power Platform. Python with Direct query in PowerBI desktop. Click on “Data Load”. Open the PBIX file in Power BI Desktop; Click on Transform Data; In Power Query Editor, delete all the queries. You may also take a look at this idea. 6. 01-13-2021 05:40 PM. DirectQuery queries the backend directly, while Import caches. You have no chance of adding measures/calculated columns into the data modle and you basically only see the source. Search for ODBC in your search bar, look for and click ODBC Data Source Administrator (64-bit) to open it. Fresh copy of this data can be downloaded by pressing Refresh button. Both tables have a UID field that is made up of a concatenation between a SiteID and timestamp. The only place when I saw the option to choose 'Import Mode' or 'Direct Query' was when choosing the Azure connection method and typing in the Server Name, Database Name and the optional SQL query. With Import Data you get the full functionality of Power BI. It doesn't make sense because the stored procedure works and returns data in power query. Direct Query is where the data resides in the database and the query pulls information when needed; Import is where the data is pulled into Power BI Desktop (this is the most commonly used method) Dual is a hybrid. , an Import Model cannot be converted back to DirectQuery. We are excited to announce. copy the code from your advanced editor to notepad. To use the Power BI SWITCH function, follow the steps highlighted below: Step 1: Open the Power BI Desktop, import the Employee Dataset dataset using the Get Data option, and click on the Load button. Once done if you switch to import mode it will just load that subset. So in other words I need to show all table A data except where value X in table A matches with value X in table B. Click on “OK” to confirm the changes. Conclusion. Earlier this year, Microsoft announced an innovative semantic model storage mode for Power BI called Direct Lake. Step 10. Direct Query Report with relationship between A and B. For import mode, you need to click the 'Refresh' buttton in Power BI Desktop, configure schedule refresh or click 'Refresh now' in Power BI Service to update data. Treat SAP HANA as a relational source: In this case, Power BI treats SAP HANA as a relational source. Launching ODBC Data Sources. 1 Answer. Direct Query Mode will show a section called "Scheduled cache refresh" (italics added by me). 5- click on Switch. Switching data source from Direct Query to Import Mode. Regards. Hi VictoriaQ, You could try to change this like below (Click View->check Field Properties, then set storage mode like below) You could refer to Storage mode in Power BI Desktop for more details. answered Dec 19, 2017 at 6:13. Add parameters. Sure, using Panoply to connect your MySQL data to Power BI takes a whopping two additional steps compared to using a direct connection. When you use Live Connection, the model is located in the data source side and consume the resource in. Sandeep Pawar. It fundamentally changes how we create and consume BI solutions. Then go into the query for the original matching table, go. Power BI service: Refresh dataflow (Image by author) 4. IMO if you're using direct query, there's a reason why. Yes, it is definitely possible. I tried to get data of the same table in direct query and I change the m language coping the m of direct query and pasting it in the table in import mode but it's still in import. Wait a moment until the DirectQuery mode changed to Import mode. 4- a message would be displayed saying that all queries must be switched to import Mode. You can even have a single table with both DirectQuery and Import partitions or a combination of tables in DirectQuery only mode, import only mode and even Dual mode (both DirectQuery and import). What is Power BI. Import Mode shines when dealing with smaller datasets under 1 GB that don’t change frequently. When you interact with the report, the imported data is shown. The Azure Cosmos DB V2 Connector for Power BI, with support for querying the Azure Cosmos DB transactional store in both DirectQuery and Import modes, is now in preview. Once done if you switch to import mode it will just load that subset. All designs/visuals should get an (X) and the message "Something's wrong. I would like to highlight some of the facts as far as speed and performance is concerned - Upon Importing the Data, the Power BI ensures the queries are loaded quickly and all changes to the visual are reflected immediately whereas while using the DirectQuery option, queries are sent to the underlying data source to. Open your Power BI model and select the report containing the dataset that you want to switch to import mode. They contain the same columns. Currently, we could change direct query to import mode, but it seems that there is no direct option for us to change import mode to direct query mode in Power BI Desktop. This approach offers greater flexibility. Putting in my own Transact-SQL (TSQL) will change the table storage Mode to ImportThe process is to: Create a new connection to one of the entities in the new dataflow from Power BI desktop (get dataPower BI Dataflows). The article emphasizes the rationale for each mode, and possible impacts on Power BI capacity resources. Koneksi menggunakan DirectQuery. Method 1 – DAX Formula Manipulation. 2- Click on Get Data, choose csv file option. Top Posts. This is really fascinating, and no doubt opens a whole lot of doors going forward. @ptepichin Yes. 10. In the Model view, when the select the table, it shows storage mode. There are many. When I connect through import it gives me an error: "The provider microsoft. you only then get to decide if you want import or Direct Query. This article targets data modelers developing Power BI DirectQuery models, developed by using either Power BI Desktop or the Power BI service. . Overview . I have joined a date table created in power bi with fact in direct query and use that. Or if you need to limit it via SQL query do the same by clicking the cog next to "Source". 0. 3- Click on Load. While the MAXX function is only supported in measure and query formulas. From what I can see you need to actually build the query again - I have not been able to work out a way of changing from import to direct query whithout starting again. However, I have previously created PBIX files that import the data. Topic Options. 4- a. To set the storage mode of a table, switch to Model View and expand the Properties pane. 1. Voila!Open Power BI desktop and click on the "Get Data" button in the Home tab. However, sorry that your request may not be fulfilled at this time. Select "Import" on the "Connection settings" page, and click OK. In This Video, We Have Demonstrated, How you can Switch Data Load from Direct To Import or Import To Direct In Power BI TAIK18#powerbi, #taik18 #directtoim. 04-19-2023 07:39 PM. I have found a way to force the switch from DirectQuery to Import. This data is stored in Power BI model. If we select the Optimization presets drop down in the Optimize ribbon, and then select Query reduction for our report, a dialogue appears describing the settings that are applied. Now, try to open the original PBIX file and it you will see that now Direct Query mode works. You can create a new query and copy the m code, but once a query is. By using this you can ask Power BI to refresh only last n number of periods (days, months,years) of data to be refreshed only while pulling from a. Or. [Colour],SUM ( CAST ( [t0]. Easiest way is to right-click on each of the query groups and click Delete Group. Query parallelization is enabled on Power BI Premium and Embedded SKUs with eight or more vCores. You could vote this similar idea in Power BI ideas Forum which has been submitted in Power BI ideas forum and add your comments there to improve Power BI. When connecting to SQL Server Analysis Services, you can choose to import the data or use a. I’ve done it for a few other groups over the last six months but this is the latest and best version, I think. In Server, enter the server name where your data is located. I do not believe this is possible. Column store in-memory technology compresses data and stores it in a compressed format. You have started building additional reports in direct query using this same dataset 3. Image 1 shows this how to manage roles. Or if you need to limit it via SQL query do the same by clicking the cog next to "Source" and enter the SQL query there. Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium This article describes DirectQuery mode for Analysis Services tabular models at the 1200 and higher compatibility levels. the report did not refresh. It fundamentally changes how we create and consume BI solutions. Switch Import to Direct Query 07-17-2017 04:10 AM. By saying “import is always there for you” instead of connecting to power bi dataset you can connect with data source and create import for your report if its fit for your requirement. It allows scheduled refreshes for up-to-date data while providing extensive. 2- Click on Get Data, choose csv file option. Click on. I have not tried this yet. For the second question, you can switch to Model view in Power BI Desktop, select a table and expand Advanced tab in the Properties pane to see which mode the table is. Edit the Snippet to add the below code. In the Power Query Editor, select New Parameters under Manage Parameters. Step 2: Change the file format. To go from Import to DirectQuery (assuming the query doesn't violate the restrictions), you need to recreate the query (you can copy and paste the query code rather than manually re. Change date format in DIRECT Query Mode - not on list of selectable formats 09-02-2019 12:46 AM. 3- Click on Load. Currently working as Data Analyst in Elsevier, a publishing and information analytics company. Power BI Pro and Power BI Premium and Embedded capacities with fewer vCores continue to process DAX and. See moreI have found a way to force the switch from DirectQuery to Import. To save measure we use to remove the table in power query and add it with the same name (direct query) . In Power BI, select the tables and views you’d like to use and click “Load. Regards7. Best Regards, Allan. Click Connect. Why You Might Want to Switch from Direct Query Mode to Import Mode in Power BI Direct Query mode in Power BI allows you to connect directly to your data. Close and Apply. Close and Apply. RLS only restricts data access for users. Connection to Oracle from Power BI using direct query works but not import. Two popular data connectivity modes are Power BI Import and Direct Query. Hoping now I am clear to you. . The purpose of this option is to support convenient time intelligence reporting based on date columns loaded into a model. Instead, it runs multiple concurrent queries on the RDBMS side. Within Power BI Desktop, I can successfully get data and populate visualizations, despite this concerning warning in the "Source" step of a query (Issue 1): The reason this is a concerning issue is I don't want to switch to "Import" mode. You'd need to create a new PBIX file, point to the new data set, then copy your visuals from your old to your new PBIX file. com) To achieve your requirement, you may connect to the datasource 2 times with import and dq separately. Sadly once the source is import you won't be able to chage it. One need not have azure datalake to use dataflow. If you’d like to learn more about import vs direct query mode, data modelling or anything about Power BI, SSAS or Azure Analysis Services, we’re here to. and all my field were refering to 'Old table' names. Besides, you may take a look at this workaround to change data source connection from Import to Direct. Net web. Click Transform Data. pbip-file in a location where you can find it easily (let's say the name of the report is MyReport. Select your datasource and Load it. Select the dataset you want to import and click on the "Data Source Settings" in the "Manage" tab. The new approach aims to provide a perfect balance between the existing import mode and. A Power BI dataset is always a semantic layer, an easily consumable interface for your BI data: in Import mode it is a semantic layer and it also stores the. selecting the tables to load allows me to check the edit button and convert to Direct Query. 2. Question: When a dataset is loaded into Power BI in DierectWuery mode, it is. Thanks, Lydia ZhangHi, I did reports using Azure SQL DataWareHouse as the Data source using Direct Query mode. Click [Apply changes] if it appears. If your data source is a SQL Server database then Power BI will generate SQL queries to get the data it needs; for other sources it may generate queries in other query languages. Go back to the report view, you will note that all visuals have been damaged. You can not edit your data by query editor, it will ask to to switch to data import mode. The query log is secure and not visible to users or through the XMLA. The table properties are in the relationship designer (in the most recent version of Power BI, I think the documents haven't been updated on the MS Docs site), there you can change it to 'Import'. One would have to switch to import mode. If you want to use any advance options, select Advanced options. Repeat for all other tables. 1. Sadly, when I published the report to the web version of Power BI and then made changes to the Excel file, it still acts like the connector is an import query i. If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Select your datasource and Load it. One need not have azure datalake to use dataflow. You have started building additional reports in direct query using this same dataset 3. Click "DirectQuer: Enabled (click to change)" on the right bottom of Power BI Desktop. The option to change the load mode is on the properties pane on the modeling view. Share Improve this answer How to Switch From Import to Direct Query Mode in Power BI I’m not sure why Microsoft sets “Import Query” as the default when creating new Power BI connections, but the tyranny of the default led me to create charts using that option. If you want to switch to import mode, you can refer to the following steps： Follow the steps described in Connect with Power BI Desktop to create a connection, using Import as the data connectivity mode. Option to Select Import & Direct Query within Power BI Direct Query – When you connect a data source using the Direct Query technique, your dashboard will. For instance, if you add a measure to your import model and then upload the new version to the service. In this article. After greating a connection to a live source, I can see this message at the bottom right corner of the desktop. The following articles provide more information about dataflows and Power BI: Introduction to dataflows and self-service data prep; Creating a dataflow; Configuring Dataflow storage to use Azure Data. If I click on it, it brings up the "Connection Settings" dialogue and I'm able to change it to "Import". For Power BI users, refreshing data typically means importing data from the original data sources into a semantic model, either based on a refresh schedule or on-demand. It seems like Power BI keeps track from where a table was originally imported. Step 2: Change the file format. Power BI Pro and Power BI Premium and Embedded capacities with fewer vCores continue to process DAX and. (1) Click model view (2) Click a table that you want to change (3) Click Advanced (4) Make your choice of directquery, import or dual. I immediately noticed that I faced some limitations. From the right gear icon, click “Edit Page” to edit the Project Center page. , an Import Model cannot be converted back to DirectQuery. In Import mode, data is moved to Power BI. In this article. With this method, you have Power Query and DAX fully functional. 5- click on Switch. One need not have azure datalake to use dataflow. As mentioned here, while it's generally possible to switch a model from DirectQuery mode to use import mode, this means all the necessary data must be imported. It's also not possible to switch back, primarily because of the set of features not supported in. By using the content database run this query. Executing the below command “ aspnet_regiis -pef connectionStrings ” will encrypt the <connectionStrings> section at a. I can find no mechanism that will allow me to convert these queries to Direct Query mode. You can change the import mode from import to direct query, but aware of the disadvantages of DQ: Power BI Switch From Import To Direct Query Mode. I have tried to do both queries as direct queries and combine them but in PowerBI this results in the direct queries being changed into an import. You will then see something like this: Because all data comes from the Power BI Desktop Cache. In this blog post I have shown how to use the new DirectQuery feature in Power BI and importing an Excel file and then creating a relationship between the DirectQuery table and the Imported table. Then delete the newly created query from a csv file. Stack Overflow. Power BI Desktop and DirectQuery. I do not believe this is possible. Full Power Query transformations, and DAX measures as well as visualizations. To connect to Azure SQL Database using DirectQuery, you must use Power BI Desktop. In Navigator, select the database. It is also not possible to switch back (primarily due to the set of features not supported in DirectQuery mode). I built all the reports. . Bascially, I am looking to understand , when get the data from source we set the Import/Direct Query toggle. Navigate to your location where you have saved your report and open the MyReport. This article describes the main differences between these concepts. For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery. Change the storage mode from Direct Query to Import Mode. Live connection is a way of connecting a Power BI report to a published Power BI semantic model. here is a more detailed screenshot that I thought might help. Hi @Anonymous , You can go to Power BI Desktop, go to Model view-->Advanced and change the storage mode from DirectQuery to Import . Here’s how to do it. Update: You may notice that Direct Query on top of some of your data flows has stopped working. [Connect directly to the data. 09-19-2018 01:24 PM. Earlier this year, Microsoft announced an innovative semantic model storage mode for Power BI. Then delete the newly created query from a csv file. We have heard your feedback that you would like to do this with sources you previously live connected to, like Power BI datasets and Azure Analysis Services sources. To avoid loading the query to the model, take care to ensure that you disable query load in these instances. This article targets data modelers developing Power BI DirectQuery models, developed by using either Power BI Desktop or the Power BI service. Import Mode: In this mode, Power BI connects with underlying data source & downloads entire data from the datasource. DirectQuery. Power Virtual Agents. Power BI reports can connect to a number of data sources. You cannot convert queries from import mode to direct query. In some situations, however, you need to discourage these connections. SelectRows (Source { [Schema=schema,Item=tablename]} [Data],each [id_file]=id_file) in GoTable. Integrate data by caching data from dataflows and. After the semantic model receives the report's DAX query, it generates another set of queries that are run on your data source, to get the required data. How to Switch From Import to Direct Query Mode in Power BI I’m not sure why Microsoft sets “Import Query” as the default when creating new Power BI connections, but the tyranny of the default. There are several reasons why you may want to switch from Direct Query to Import mode in Power BI: Performance: If your data source is large or complex,. @amitpatil35 Hi, on the status bar bottom right corner, there's a text that reads "Direct Query: On (Click Here To Change)", if you click that and pick Import, your model should start importing. 3- Click on Load. Using DirectQuery with Power BI dataflows lets you connect directly to a dataflow without the need to import the data into a semantic model. In Power BI Desktop, there is two connectivity mode when you connect to a data source: 1. 4921. Like importing data, the dual storage mode caches the data in the table. Press Ctrl + V. When you first GET DATA -> SQL -> server name -> pick table -> load->. I understand, Copy paste visuals from import pbix file to direct query as long as the meta data matches, works fine but i dont want to go this route. How we can use Direct Query when loading data into Power BI Desktop@amitpatil35 Hi, on the status bar bottom right corner, there's a text that reads "Direct Query: On (Click Here To Change)", if you click that and pick Import, your model should start importing. Community Accounts & Registration. You can use import mode if your data is less than 1 GB or if your data does not change frequently. . I had a report made with tables in import, I need to have all in direct query. 1. So in such scenario, an import mode is faster than Direct Query mode when the data file size is below 1 GB. , an Import Model cannot be converted back to DirectQuery. Still it may not work. 09-26-2022 06:33 PM. Accoridng to the official document , Can't change from import to DirectQuery mode: While it's possible to switch a model from DirectQuery mode to use import mode, all the necessary data must be imported. The three semantic model modes are: Import. A new storage mode got introduced at the same time called Dual. For relational DB data sources, you can right-click the last applied step in the Query Settings pane. Import. In this article. Additionally, if your table size is close to the Power BI SKU limit for Import datasets, it probably makes sense to switch to Direct Query mode. Improve this answer. We released a new feature that allows you to control which dataflows can. I’ve worked with several customers. Within Power BI Desktop, clicking on “File –> Import –> Power BI Template”. To set the Storage mode property, or view its current setting: In Model view, select the table whose properties you want to view or set. You now want to decouple the reports from the dataset or you wish to split an unweildy report with lots of pages into sdperate reports. Voila!You are correct about the difference. Both are currently running as Direct Query but I would like to change one of them to import. The aggregation feature interacts with table-level storage modes. PowerBI table does not show value when using "Direct query" table. I've a SQL database that contains a significant amount of data that I don't really want to import, so Power BI's DirectQuery concept is. It applies to semantic models that represent a live connection to an external-hosted Analysis Services model, and also to models developed in Power BI Desktop. and all my field were refering to 'Old table' names. If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you're connected by Direct Query, you'll see the message "DirectQuery: Enabled (Click to Change)" in the status bar at the bottom right hand screen on Power BI Desktop:. Live connection. Open edit Query And try to transform data like unpivot table, etc. You can choose ‘Model View’ in the left pane, select the corresponding table using ‘DirectQuery’ storage mode, go to ‘Properties’, change the storage mode from ‘DirectQuery’ to ‘Import’. I have a SQL Azure database that I can use Direct Query with for a new PBIX file. I would like to join this with an Imported table (Buckles) from an Excel sheet sourced from SharePoint Online. Live connection. Press Ctrl + C. oledb is not registered". Why is this so hard to do? First it hung because the preview wasn't refreshed. From what I can see you need to actually build the query again - I have not been able to work out a way of changing from import to direct query whithout starting again. In the short term, if you can't switch to import mode (recommended to get the flexibilty you need and improve performance), you can use the SUMMARIZE function in your measures to get the distinct combination of the fields you need (e. If you use connect live, there's no query defined, and the entire external model is shown in the field list. See comments in: Introducing Power BI Report Server for on-premises Power BI report publishing .