Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Hi Lydia
Thank you for clearing this up! Makes sense now.
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?
Hi ValubiMartin
Thank you for the advice, I have just tried and it did not work for me.
And yes I am danish 🙂
Br
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |