I am using an MDX query in Power BI to extract data from SQL Server Analysis Services, but for some reason the query is repeating every column twice.
I don't have the knowledge of MDX query, so I used report builder for this.
The query:
SELECT
NON EMPTY { [Measures].[Premium LCL] } ON COLUMNS,
NON EMPTY { (
[D_BU].[Business Unit Number].[Business Unit Number].ALLMEMBERS *
[D_Policy].[OriginalPolicyID].[OriginalPolicyID].ALLMEMBERS *
[D_Revenue_Attribute].[InsurerName].[InsurerName].ALLMEMBERS *
[D_Client].[ClientName].[ClientName].ALLMEMBERS *
[D_TransactionDate].[Accounting Month Number].[Accounting Month Number].ALLMEMBERS * [D_TransactionDate].[Accounting Year].[Accounting Year].ALLMEMBERS *
[D_TransactionDate].[Accounting Month Year].[Accounting Month Year].ALLMEMBERS * [D_PolicyExpirationDate].[Expiration Month Year].[Expiration Month Year].ALLMEMBERS * [D_Policy].[InceptionDate].[InceptionDate].ALLMEMBERS *
[D_Product].[Product Line].[Product Line].ALLMEMBERS *
[D_Product].[Product Coverage].[Product Coverage].ALLMEMBERS *
[D_BU].[Region Name].[Region Name].ALLMEMBERS *
[D_BU].[Branch Name].[Branch Name].ALLMEMBERS *
[D_CarrierPayee].[Placement Market Group].[Placement Market Group].ALLMEMBERS * [D_Industry_SIC].[SIC Level 2 Name].[SIC Level 2 Name].ALLMEMBERS *
[D_Industry_SIC].[SIC Level 3 Name].[SIC Level 3 Name].ALLMEMBERS
) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_VALUE,
MEMBER_UNIQUE_NAME ON ROWS
FROM
(
SELECT
(
{[D_BU].[RegionName].&[GGB UK - Underwriting],
[D_BU].[RegionName].&[GGB UK - Specialty],
[D_BU].[RegionName].&[GGB(UK & Ireland)] }
) ON COLUMNS
FROM
(
SELECT
(
-{ [D_BU].[BranchName].&[Re Centre],
[D_BU].[BranchName].&[UK&Ireland] }
) ON COLUMNS
FROM
(
SELECT
(
{ [D_TransactionDate].[Accounting Year].&[2023] }
) ON COLUMNS
FROM
[Model]
)))
WHERE ( [D_BU].[RegionName].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS