Assistance Needed with Power BI Visual for Dynamic Database Switching

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.