Datesbetween mdxscript error

[ragoe]

Hi all

I am trying to count the number of days in the selection of my date slicer.

I have a meassure: TA2 = DATESBETWEEN(Datotabel[Date];FIRSTDATE(Datotabel[Date];LASTDATE(Datotabel[Date]))

My date tabel only has distinct dates.

And I get this error (Several values were delivered where only one was expected):

Feedback Type:
Frown (Error)

Timestamp:
2017-07-10T10:06:04.4874889Z

Local Time:
2017-07-10T12:06:04.4874889+02:00

Product Version:
2.47.4766.801 (PBIDesktop) (x64)

Release:
June 2017

IE Version:
11.413.15063.0

OS Version:
Microsoft Windows NT 10.0.15063.0 (x64 da-DK)

CLR Version:
4.6.2. or later [Release Number = 460798]

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

Peak Working Set:
656 MB

Private Memory:
737 MB

Peak Virtual Memory:
34.3 GB

Error Message:
MdxScript(Model) (73, 25) Beregningsfejl i måling 'Datotabel'[TA2]: Der blev leveret flere værdier, hvor der kun blev forventet en enkelt.

User ID:
0a555c0d-19f4-4c8e-b79d-cbd1ebfd4e1e

Session ID:
2240dd3b-19be-4908-bde0-367f62ea2fd4

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\ragoe\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot198116231.zip

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

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_allowBiDiCrossFilterInDirectQuery
PBI_pivotTableVisualEnabled
PBI_numericSlicerEnabled
PBI_daxTemplatesEnabled
PBI_relativeDateSlicer

Disabled Preview Features:
PBI_Snowflake
PBI_SpanishLinguisticsEnabled
PBI_PbiServiceLiveConnect
CustomConnectors

Enabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

Klokkeslæt:
Mon Jul 10 2017 12:04:11 GMT+0200 (Rom, sommertid)

Error Code:
QueryUserError

OData Error Message:
Failed to move the data reader to the next row.

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

shared Datotabel = let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2017, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2017,1,1))+1, #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}})
in
    #"Changed Type2";

What is causing this error for me?

[Anonymous · Accepted solution]

@ragoe,

DATESBETEEN function is always used along with CALCULATE function, in your scenario, please use the following DAX to create TA2.

TA2 = CALCULATE (
    DATEDIFF ( FIRSTDATE(Datotabel[Date]), LASTDATE(Datotabel[Date]), DAY),
    ALLSELECTED (Datotabel[Date])
)

Regards,
Lydia

[ragoe]

Hi Lydia

Thank you for clearing this up! Makes sense now.

[Anonymous]

Hi ragoe,

I think I know why this is happening. Sometimes, for unknown reasons, Power BI misinterpret whether dates are unique or not. What you have to is go to Edit query, left-click on the the header of the column with dates, choose "Remove duplicates", and then close & apply. I can illustrate with pictures if needed.

And are you by any chance danish?

[ragoe]

Hi ValubiMartin

Thank you for the advice, I have just tried and it did not work for me.

And yes I am danish 🙂

Br

[Anonymous]

Hi again ragoe,

Okay, combine aforementioned advice with this: edit query -> right click header of date column -> remove errors -> change data type to date. Make sure that you aren't deleting any rows.

Let me know how this goes and nice to see some danish activity on here 🙂  

Martin

[ragoe]

Hi Martin

When I select a single date the measurre shows me the date, but when I select muliple dates or use a the dateslicer with the between dates slider it fails when the same error message.

Br