Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I am trying to improve the effiency of the query below.
The database I am reaching is huge but the data I need is not large. I thought that adding the filters in the end would have improved performance but it still thanks a lot of time to to update the query or refresh the data.
I am probably missing some basic good practice to get it working properly.
I cannot do a live link to the dataset as some of the data will have to be transformed afterwards.
Thanks in advance.
let Source = AnalysisServices.Databases("link://xxx.azurewebsites.net", [TypedMeasureColumns=true, Implementation="2.0"]), #"MS - Orders1" = Source{[Name="MS - Orders"]}[Data], #"MS - Orders2" = #"MS - Orders1"{[Id="MS - Orders"]}[Data], #"MS - Orders3" = #"MS - Orders2"{[Id="MS - Orders"]}[Data], #"Added Items1" = Cube.Transform(#"MS - Orders3", { {Cube.AddAndExpandDimensionColumn, "[Dim_Customers]", {"[Dim_Customers].[Hrchy_Customers].[Billing Account]", "[Dim_Customers].[Hrchy_Customers].[Corporate Account]", "[Dim_Customers].[Hrchy_Customers].[Sales Account]"}, {"Dim_Customers.Billing Account.3", "Dim_Customers.Corporate Account.3", "Dim_Customers.Sales Account.2"}}, {Cube.AddAndExpandDimensionColumn, "[Dim_Date]", {"[Dim_Date].[Date].[Date]", "[Dim_Date].[Month].[Month]", "[Dim_Date].[Month (Date)].[Month (Date)]", "[Dim_Date].[Month#].[Month#]", "[Dim_Date].[Quarter].[Quarter]", "[Dim_Date].[Quarter#].[Quarter#]", "[Dim_Date].[Year].[Year]", "[Dim_Date].[Year (Date)].[Year (Date)]", "[Dim_Date].[YearMonth].[YearMonth]", "[Dim_Date].[YearMonth (PI Display)].[YearMonth (PI Display)]", "[Dim_Date].[YearQuarter].[YearQuarter]", "[Dim_Date].[Hrchy_Date].[Year (Date)]", "[Dim_Date].[Hrchy_Date].[Month (Date)]", "[Dim_Date].[Hrchy_Year_Date].[Year]", "[Dim_Date].[Hrchy_Year_Date].[YearQuarter]", "[Dim_Date].[Hrchy_Year_Date].[YearMonth]"}, {"Dim_Date.Date.1", "Dim_Date.Month.1", "Dim_Date.Month (Date).3", "Dim_Date.Month#.1", "Dim_Date.Quarter.1", "Dim_Date.Quarter#.1", "Dim_Date.Year.3", "Dim_Date.Year (Date).3", "Dim_Date.YearMonth.3", "Dim_Date.YearMonth (PI Display).1", "Dim_Date.YearQuarter.3", "Dim_Date.Year (Date).2", "Dim_Date.Month (Date).2", "Dim_Date.Year.2", "Dim_Date.YearQuarter.2", "Dim_Date.YearMonth.2"}}, {Cube.AddAndExpandDimensionColumn, "[Dim_Product_Line]", {"[Dim_Product_Line].[Product Line (Code)].[Product Line (Code)]", "[Dim_Product_Line].[Product Line (Desc)].[Product Line (Desc)]"}, {"Dim_Product_Line.Product Line (Code).1", "Dim_Product_Line.Product Line (Desc).1"}}, {Cube.AddAndExpandDimensionColumn, "[Dim_xxx_Organization]", {"[Dim_xxx_Organization].[Country (Code)].[Country (Code)]", "[Dim_xxx_Organization].[Country (Desc)].[Country (Desc)]", "[Dim_xxx_Organization].[Geomarket (Code)].[Geomarket (Code)]", "[Dim_xxx_Organization].[Geomarket (Desc)].[Geomarket (Desc)]"}, {"Dim_xxx_Organization.Country (Code).1", "Dim_xxx_Organization.Country (Desc).1", "Dim_xxx_Organization.Geomarket (Code).1", "Dim_xxx_Organization.Geomarket (Desc).1"}}, {Cube.AddAndExpandDimensionColumn, "[Fact_MandS_Orders]", {"[Fact_MandS_Orders].[Field Ticket Ref #].[Field Ticket Ref #]", "[Fact_MandS_Orders].[FTL Approval Date].[FTL Approval Date]", "[Fact_MandS_Orders].[FTL Approver ID].[FTL Approver ID]", "[Fact_MandS_Orders].[FTL Submission Date].[FTL Submission Date]", "[Fact_MandS_Orders].[iDistrict/eTrace#].[iDistrict/eTrace#]", "[Fact_MandS_Orders].[Invoice Create Date].[Invoice Create Date]", "[Fact_MandS_Orders].[Invoice Post Date].[Invoice Post Date]", "[Fact_MandS_Orders].[Order Approval Date].[Order Approval Date]", "[Fact_MandS_Orders].[Order Creation Date].[Order Creation Date]", "[Fact_MandS_Orders].[Order Creator Job Category].[Order Creator Job Category]", "[Fact_MandS_Orders].[Order First Submission Date].[Order First Submission Date]", "[Fact_MandS_Orders].[Order Job End Date].[Order Job End Date]", "[Fact_MandS_Orders].[Order Type].[Order Type]", "[Fact_MandS_Orders].[Revenue Status].[Revenue Status]", "[Fact_MandS_Orders].[Siebel Order ID].[Siebel Order ID]", "[Fact_MandS_Orders].[Stage 1].[Stage 1]", "[Fact_MandS_Orders].[Stage 5].[Stage 5]", "[Fact_MandS_Orders].[Stage_2].[Stage_2]", "[Fact_MandS_Orders].[Stage_3].[Stage_3]", "[Fact_MandS_Orders].[Stage_4].[Stage_4]"}, {"Fact_MandS_Orders.Field Ticket Ref #.1", "Fact_MandS_Orders.FTL Approval Date.1", "Fact_MandS_Orders.FTL Approver ID.1", "Fact_MandS_Orders.FTL Submission Date.1", "Fact_MandS_Orders.iDistrict/eTrace#", "Fact_MandS_Orders.Invoice Create Date.1", "Fact_MandS_Orders.Invoice Post Date", "Fact_MandS_Orders.Order Approval Date.1", "Fact_MandS_Orders.Order Creation Date.1", "Fact_MandS_Orders.Order Creator Job Category", "Fact_MandS_Orders.Order First Submission Date", "Fact_MandS_Orders.Order Job End Date.1", "Fact_MandS_Orders.Order Type.1", "Fact_MandS_Orders.Revenue Status.1", "Fact_MandS_Orders.Siebel Order ID.1", "Fact_MandS_Orders.Stage 1.1", "Fact_MandS_Orders.Stage 5.1", "Fact_MandS_Orders.Stage_2.1", "Fact_MandS_Orders.Stage_3.1", "Fact_MandS_Orders.Stage_4.1"}}, {Cube.AddMeasureColumn, "Revenue (kUSD).1", "[Measures].[Revenue (kUSD)]"}, {Cube.AddMeasureColumn, "Stage 2.1", "[Measures].[Stage 2]"}, {Cube.AddMeasureColumn, "Stage 3.1", "[Measures].[Stage 3]"}, {Cube.AddMeasureColumn, "Stage 4.1", "[Measures].[Stage 4]"}, {Cube.AddMeasureColumn, "Total DIR Stage.1", "[Measures].[Total DIR Stage]"} }), #"Filtered Rows" = Table.SelectRows(#"Added Items1", each (Cube.AttributeMemberId([Dim_Date.Year.2]) = "[Dim_Date].[Hrchy_Year_Date].[Year].&[2019]" meta [DisplayName = "2019"]) and (Cube.AttributeMemberId([Fact_MandS_Orders.Revenue Status.1]) = "[Fact_MandS_Orders].[Revenue Status].&[Invoiced]" meta [DisplayName = "Invoiced"]) and (Cube.AttributeMemberId([Fact_MandS_Orders.Order Type.1]) = "[Fact_MandS_Orders].[Order Type].&[Sales Order]" meta [DisplayName = "Sales Order"])), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [#"Revenue (kUSD).1"] <> null and [#"Revenue (kUSD).1"] <> "") in #"Filtered Rows1"
Hi @Anonymous ,
I would suggest you handling these data at data source rather than in PowerBI, you can add filters in data source, if you add it in Power Query, these data has been imported into PowerBI.
Best Regards,
Teige
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
69 | |
62 | |
55 |