Custom sorting a slicer does not work

Copper Contributor

Hello all,

 

For some strange reason, I suddenly cannot sort a slicer using a custom list. I create the list, double click on it with the slicer selected- nothing. I delete the slicer, create the list, create the slicer again- nothing. This used to work fairly recently and I cannot understand what the issue is. Has anyone encountered something similar and how did you fix it?

 

Regards!

15 Replies

@dzhogov I am having a similar issue too. I have created a Data Model for my data and am using a Slicer for Months. But the Months are not being displayed in a monthly sorting manner (Jan, Feb .. Dec). I have tried default sorting, created custom sorts but nothing works. Looks like when using the Data Model, there are issues when using sorting.

@gaujla , you shall define sorting order. Add one more column with month numbers and select for Months column Sort By these numbers.

image.png

 

@Sergei Baklan Thank you very much Sir! that worked beautifully. I was using the same technique but at the wrong location. I was defining the MONTHNUM in my original Data tab but it wasn't working. Slicer kept on sorting my data using the original date (source data order). But as soon as I created the MONTHNUM column in my Data Model, and used the sort MONTH by MONTHNUM, it worked like magic. Thanks a lot.

 

Another question, my original Data doesn't have any "blank" rows/column/records, but the Pivot Table/Slicer keeps on thinking there is "blank" data. I have unchecked the various options to hide or not show blank data but isn't working. Any help would be much appreciated. 

@gaujla , it depends on where do you have such blanks, could you give bit more details?

@Sergei Baklan "blanks" as a field is appearing in the Pivot Table options. It appears on all Column Headings. I don't have any blank rows of data or blank columns in my data. Also, I have a defined table as data source which is supposed to be expandable as time goes on and users make more entries on a daily basis.

@Sergei Baklan I meant "blank" as a record in appearing in Months, Quarters, or whichever Field I use for my Pivot Table. I don't have any "blank" records/columns/cells/rows in my data. Although the source table is dynamic not static, meaning, my source table is $A:W$ and not $A1:$W1048576

@gs_aujla , if your source table is till end of the sheet you shall have blank rows. However, with correct aggregation PivotTable shall ignore them. Try to select all rows within the range after last row with data,  Home->Clear->Clear All, after that Save file and refresh your PivotTable.

 

As for the slicer you may in slicer setting Hide items with no data.

@Sergei Baklan Hello Sir, tried doing that, doesn't work. I tried both Clear>Clear All and Clear>Contents and also Delete All Rows after my last record, it doesn't work. Just to mention, when I was created the same Pivot Table earlier without using the Data Model, the "blank" option wasn't there or I was able to ignore it completely and the Pivot Table options would remember it. It's only after I created my Pivot Table using the Data Model option, that this issue has occurred. I need the Data Model option because I created my own Measure that I need to have a calculated field that gives the result in Text form and not Numerical/Mathematical form.

@gs_aujla ,

So, you have no empty string cells. That is strange if you have EXACTLY the same Pivot table with and without data model with different results here. Perhaps relationships are different, blank row label could appear if no matching rows in related (child) table. Something with aggregation.

 

Unfortunately I'm not so deep in DAX to say something more concrete without playing with file.

@Sergei BaklanHi .. I am creating a new dashboard with a new Pivot Table and it's own Data Model to use DAX. But my new Excel worksheet keeps on calling the old Data Model for a different Excel workbook. Can't I have multiple Data Models for different worksheets in my domain?

@Sergei Baklan Also, one of my fields in my source table is set to Number but the Pivot Table with Data Model is not able to use Sum function to calculate that field. But when I use a normal Pivot Table without Data Model, it is able to do that. Not sure why is that so.

 

Help Please!

============================
Error Message:
============================

The following system error occurred: Type mismatch.
Datatype conversion failed for [Table: 'PNWTable', Column: 'COMPLIANCE-Y', Value: ''].

----------------------------
The following system error occurred: Type mismatch.
Datatype conversion failed for [Table: 'PNWTable', Column: 'COMPLIANCE-Y', Value: ''].


============================
Call Stack:
============================

at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ProcessResults(XmlaResultCollection results)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo.DoExecuteEngineCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteEngineCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
----------------------------
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
at Microsoft.AnalysisServices.Common.SandboxEditor.DataTypeSelectionChanged(String newSelectedString, TableWidgetGridControl grid, List`1 selectedColumns)

============================

 

 

Above is the error I am getting on my Power Pivot field.

@gs_aujla , perhaps you have some empty cells (not blank but cells with empty string ""). SUM within Excel sheet, includes PivotTable aggregation, ignores any texts. 

 

Data model returns conversion error in such case, SUM and like here don't work with strings. All shall be numbers or blanks.

@Sergei Baklan Yes looks like that is the issue. Once I delete empty rows at the bottom, I am not getting the issue. Although I have one issue, how can I make my source data table dynamic so as the user can just start typing in the input values after the last record without having to "tab" through the last column of the last existing record or without the need to manually pull the dynamic table ribbon column on the last record.

@gs_aujla , but table shall autoexpand if you type in any cell of the next row and after that Enter, or Arrow Left/Right (other words change focused cell). Tab in right cell of the last row is not necessary.