Problem on Hierarchy Slicer

[eddiechang]

Hi All,

I realized there problem for select all when the list are more than 140 items.

There no problem if the product list is small.

Kindly help. Thanks.

Feedback Type:
Frown (Error)

Timestamp:
2017-12-18T04:36:59.7857560Z

Local Time:
2017-12-18T12:36:59.7857560+08:00

Session ID:
72da3036-2df3-4841-8945-fb0cb6631ea8

Release:
November 2017

Product Version:
2.52.4921.581 (PBIDesktop) (x64)

Error Message:
The specified query is too complex to be evaluated as a single statement.

OS Version:
Microsoft Windows NT 10.0.15063.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 460798]

Peak Virtual Memory:
34 GB

Private Memory:
361 MB

Peak Working Set:
737 MB

IE Version:
11.483.15063.0

User ID:
2d3b96d3-0852-45f7-9224-c48cae2567d7

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\Power-BI\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot312777470

Performance Trace Logs:
C:\Users\Power-BI\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_reportBookmarks

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

Activity ID:
null

Time:
Mon Dec 18 2017 12:36:54 GMT+0800 (Malay Peninsula Standard Time)

Error Code:
QueryUserError

OData Error Message:
Failed to execute the DAX query.

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared bi_dbcr_master = let
Source = Odbc.DataSource("dsn=BI.LYC", [HierarchicalNavigation=true]),
bi_lyc_Database= Source{[Name="bi_lyc",Kind="Database"]}[Data],
bi_dbcr_master_Table = bi_lyc_Database{[Name="bi_dbcr_master",Kind="Table"]}[Data],
#"Replaced Value" = Table.ReplaceValue(bi_dbcr_master_Table,null,"N/A",Replacer.ReplaceValue,{"DBCRAreaCode", "DBCRAreaName", "DBCRStateCode", "DBCRStateName", "DBCRPostcode", "DBCRPostcodeName", "DBCRCountryCode", "DBCRCountryName"})
in
#"Replaced Value";

shared bi_product_master = let
Source = Odbc.DataSource("dsn=BI.LYC", [HierarchicalNavigation=true]),
bi_lyc_Database = Source{[Name="bi_lyc",Kind="Database"]}[Data],
bi_product_master_Table = bi_lyc_Database{[Name="bi_product_master",Kind="Table"]}[Data],
#"Replaced Value" = Table.ReplaceValue(bi_product_master_Table,null,"N/A",Replacer.ReplaceValue,{"ProductCategoryName"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"N/A",Replacer.ReplaceValue,{"PoductCategoryCode", "ProductCategoryName", "ProductColourCode", "ProductColourName", "ProductSizecode", "ProductSizeName", "ProductBrandCode", "ProductBrandName", "ProductModelCode", "ProductModelName"})
in
#"Replaced Value1";

shared bi_salesman_master = let
Source = Odbc.DataSource("dsn=BI.LYC", [HierarchicalNavigation=true]),
bi_lyc_Database = Source{[Name="bi_lyc",Kind="Database"]}[Data],
bi_salesman_master_Table = bi_lyc_Database{[Name="bi_salesman_master",Kind="Table"]}[Data],
#"Replaced Value" = Table.ReplaceValue(bi_salesman_master_Table,null,"N/A",Replacer.ReplaceValue,{"SalesmanGroupCode", "SalesmanGroupName"})
in
#"Replaced Value";

shared bi_store_master = let
Source = Odbc.DataSource("dsn=BI.LYC", [HierarchicalNavigation=true]),
bi_lyc_Database = Source{[Name="bi_lyc",Kind="Database"]}[Data],
bi_store_master_Table = bi_lyc_Database{[Name="bi_store_master",Kind="Table"]}[Data]
in
bi_store_master_Table;

shared bi_bill = let
Source = Odbc.DataSource("dsn=BI.LYC", [HierarchicalNavigation=true]),
bi_lyc_Database = Source{[Name="bi_lyc",Kind="Database"]}[Data],
bi_bill_Table = bi_lyc_Database{[Name="bi_bill",Kind="Table"]}[Data]
in
bi_bill_Table;

shared LastRefresh = let

TodaysDate = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),8)) + #duration(0,8,0,0),

#"Converted to Table" = #table(1, {{TodaysDate}}),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh Date Time"}}),

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh Date Time", type datetime}})

in

#"Changed Type";

shared MeasureDimension = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzEktVorViVYyAvICSxPzSjJLKsECxkCBoNTk1MyCEgXn/NK8EqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Measure = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Measure", type text}})
in
#"Changed Type";

[bajee]

This issue can be  Datasource credentail also try to set the correct credentails.

[v-ljerr-msft · Accepted solution]

Hi @eddiechang,

Are there any complex measures you're using on the report? If yes, could you check the logic there and try to refine them for better performance to see if it helps?

From the trace log above, I saw that you're using an old version of Power BI Desktop. Could you try downloading and installing the latest version to see if the issue persists?

If the issue persists, I would also suggest you try to contact the author(Jan Pieter Posthuma) of Hierarchy Slicer custom visual to give your feedback, in order to improve Hierarchy Slicer in future release. 

Regards