Automated Unit Testing
Hi Forum, Inspired by this article, I'd like to create automated unit tests to test measures within my Power BI Semantic Model (Dataset). I'm planning to: - Use Powershell to connect to and run through the tests - DAX scripts to query the Semantic Model itself Since these are unit tests, I want to contain "fake" data within the test itself (not have to worry about the underlying database changing). A few approaches I've attempted. 1. Overwrite Existing Table for Test (Doesn't work; PBI won't let me overwrite the actual_table in the semantic model) DEFINE TABLE actual_table = UNION(ROW("order","A1"),ROW("order","B1")) -- attempt to update as a "local variable" MEASURE 'Measures Table'[COUNT_ORDERS] = DISTINCTCOUNT('actual_table'[order])) EVALUATE ROW("COUNT_ORDERS",'Measures Table'[COUNT_ORDERS]) Error: Table '<ccon>actual_table</ccon>' cannot be created because a table or variable with the same name already exists. 2. Update Table Reference (Pretty sure PBI was designed purposely not to do this) DEFINE TABLE test_table = UNION(ROW("order","A1"),ROW("order","B1")) MEASURE 'Measures Table'[COUNT_ORDERS] = DISTINCTCOUNT('actual_table'[order])) -- somehow update table reference 'actual_table' to 'test_table' EVALUATE ROW("COUNT_ORDERS",'Measures Table'[COUNT_ORDERS]) 3. SWITCH between actual table and test table (I don't like this approach because I'd have to duplicate the SWITCH across all measures) DEFINE TABLE test_table = UNION(ROW("order","A1"),ROW("order","B1")) MEASURE 'Measures Table'[COUNT_ORDERS] = SWITCH(2, 1,DISTINCTCOUNT('actual_table'[order]), 2,DISTINCTCOUNT('test_table'[order]) ) EVALUATE ROW("COUNT_ORDERS",'Measures Table'[COUNT_ORDERS]) Do you have any alternative recommendations? Your help is greatly appreciated.
Power BI Datasets On-Premises
Hello, we are using Power BI on-premises with the Power Report Server. There is no opportunity to use Power BI Service. I would like to create Power BI Datasets and use them for several Power BI Reports and also for Paginated Reports. Is this possible on-premises? Best regards FabianSlicer enabling user to choose dataset for several visuals
Hi PBI community 😊, Users of my report would like to be able to choose between two different datasets for several visuals. They want to choose between two types of calculation rules/datasets depending on their needs. Is there a way to create a slicer to choose between two different datasets ? I was thinking of using bookmarks but this feature does not work well when the report is exported on upslide (powerpoint), which is needed by the users. Thank you for your help Gaëlle
Formula to take into account missing data from one dataset
Hi, I have the following data sets: - List of all employees that have worked for the company (includes start and end date of employment) - List of shifts worked each month My end goal is to report on how many of our active employees were/are 'utilised' each month. The attached screenshot shows what I've achieved so far. I've managed to get a utilisation figure by dividing 'is employee utilised?' by 'employee count', but the issue I'm having is that it currently doesn't take into account 'Start Date'. In the example, employee 383 isn't being utilised, but they are an active employee, as their start date is lower than the year/month column. The reason it doesn't is because they didn't work that month and therefore aren't present on my 'list of shifts worked each month' dataset. Can anyone think of a way around this? Ideally I just need to say =If ('start date calc' < 'year/month'), 1,0)... but this won't work as the employee in question isn't present on the shifts dataset. Thanks! Let me know if you need any more info.
Dataset Refresh Automation Using Power Automate
Hello, I'm currently using Power Automate to kick off dataset refreshes automatically based on keywords used in a group chat. With that said, I'm curious about a few things: Is there any way to kick off refreshes of data sets without using PowerAutomate in a specific sequence that I choose based on importance in the organization? If the above is not possible. Then, is there a way for Power Automate to just add the new datasets to the Flow so that someone doesn't have to manage the flow and constantly add the datasets (we're a big org that creates new data sets daily). The end goal is this: Refresh all data sets in a programmatic way instead of having to go to the PBI Server and refresh when a report breaks. In this day of technology, I'm sure someone out there is doing this the right, efficient way.
Connect to Power BI Dataset using XMLA EndPoint Authentication
Hi, We have a requirement to have a backup solution that allows end users to view critical reports in the event of Azure Active Directoy AAD going down (like what happened last year for a number of hours). Currently our planned backup solution will consist of using Power BI Report server. Currently all our reports, a combination of Desktop and paginated reports use live connections to a number of datasets published to the Power BI Service. Our backup solution aims to create data sources and datasets on the Power BI Report server from these datasets using XMLA endpoints and scheduling refreshes once a day so in the event that the Power BI Service goes down or AAD going down for a period of time we have this temporary backup of datasources and reports on Power BI Report server to use until the Power BI Service is backup and running. This may never happen but we were asked to have a solution in place if it does. Our issue currently: We are using a embedded capacity NOT a premium capacity at the moment. We are trying to connect to the datasets on the Power BI Service from Power BI Report server using the XMLA endpoint. Details of how to do this were taken from the "Hybrid Use Case" section in the link below https://www.bluegranite.com/blog/tips-for-a-successful-power-bi-report-server-implementation . We have the XMLA endpoint enabled on the service but we are unable to connect to the dataset just yet. We are getting errors as we are not entirely sure which authentication method to use. Note we did try the belwo connection string with spaces also. Firstly, do we require a Premium capacity for this to work properly? Secondly, if not what authentication method should we use? Thanks.
