Dear Power BI Support Team,
I am encountering issues with a Power BI project and would appreciate your assistance. Below are the details:
Project Description:
SQL Server: PragyanXRMLabs
Databases: DB1 and DB2
Tables: Both databases have a "Regions" table with identical structures but different data.
Requirement:
I need to build a tabular visual for the "Regions" table with a slicer offering two values: GB and NI. The visual should display data from DB1 (related to London) when GB is selected and data from DB2 (related to Northern Ireland) when NI is selected. A common login with access to both databases is available.
Issues Encountered:
1: DirectQuery Limitation:
Upon attempting to load data, the following error message appears:
Couldn't load the data for this visual DirectQuery error: DirectQuery may not be used with this data source - please consider moving to a supported data source or upgrading the SQL Server data source to the latest available version.
2: File Corruption:
After saving the PBIX file, it becomes corrupted and cannot be reopened. The error message displayed is:
Something went wrong Value cannot be null. Parameter name: Non-null assertion failure.
Setup:
I have created a table named "databases" for the slicer:
Id UniqueName ServerName DatabaseName
1 GB PragyanXRMLabs DB1
2 NI PragyanXRMLabs DB2
Here is the SQL script used to create the "Regions" table and some sample data for both databases:
-- DB1 Setup
USE [DB1]
GO
CREATE TABLE [dbo].[Regions]( NOT NULL, NOT NULL, NOT NULL, CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED ([Code] ASC) ) ON [PRIMARY]
GO
INSERT INTO [Regions] (Code, Region, Copier) VALUES ('GL1', 'Gloucester Area', 'London'), ('GL2', 'Gloucester Area', 'London');
-- DB2 Setup
USE [DB2]
GO
CREATE TABLE [dbo].[Regions]( NOT NULL, NOT NULL, NOT NULL, CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED ([Code] ASC) ) ON [PRIMARY]
GO
INSERT INTO [Regions] (Code, Region, Copier) VALUES ('NI1', 'Belfast Area', 'Northern Ireland'), ('NI2', 'Belfast Area', 'Northern Ireland');
M Query for Regions:
let
// Assuming SelectedUniqueName is defined earlier in your code
selectedUniqueName = #"SelectedUniqueName",
// Conditional logic to set ServerName and DatabaseName based on SelectedUniqueName
ServerName = if selectedUniqueName = "GB" then "PragyanXRMLabs" else if selectedUniqueName = "UDB2" then "PragyanXRMLabs" else null,
DatabaseName = if selectedUniqueName = "NI" then "DB1" else if selectedUniqueName = "DB2" then "VantageFaxcoNI" else null,
// Check if both ServerName and DatabaseName are not null
Source = if ServerName <> null and DatabaseName <> null then
Sql.Database(ServerName, DatabaseName,
[Query=
"
SELECT
*
FROM
Regions R
WHERE
Code = 'GL1'
"])
else
error "Invalid SelectedUniqueName or missing Server/Database configuration."
in
Source
Despite these configurations, the file becomes corrupted upon saving, and I encounter the mentioned errors.
Could you please provide guidance or suggestions to resolve these issues?
Thank you for your time and assistance.
