Forum Discussion

dzhogov's avatar
dzhogov
Copper Contributor
Apr 05, 2019

Custom sorting a slicer does not work

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 l...
excel
gs_aujla's avatar
gs_aujla
Copper Contributor
May 06, 2019

SergeiBaklan 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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 06, 2019

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 08, 2019

    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.

  • gs_aujla's avatar
    gs_aujla
    Copper Contributor
    May 08, 2019

    SergeiBaklan 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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 08, 2019

    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.

  • gs_aujla's avatar
    gs_aujla
    Copper Contributor
    May 08, 2019

    ============================
    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's avatar
    gs_aujla
    Copper Contributor
    May 08, 2019

    SergeiBaklan 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!

  • gs_aujla's avatar
    gs_aujla
    Copper Contributor
    May 08, 2019

    SergeiBaklanHi .. 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?

Resources