cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eddiechang Regular Visitor
Regular Visitor

Problem on Hierarchy Slicer

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.

hie sl.PNG

 

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";

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Problem on Hierarchy Slicer

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. Smiley Happy

 

Regards

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Problem on Hierarchy Slicer

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. Smiley Happy

 

Regards

bajee Member
Member

Re: Problem on Hierarchy Slicer

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 259 members 2,800 guests
Please welcome our newest community members: