cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ragoe
Frequent Visitor

Datesbetween mdxscript error

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?

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia

 

Thank you for clearing this up! Makes sense now.

Anonymous
Not applicable

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
Frequent Visitor

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
Not applicable

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
Frequent Visitor

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!