The following command is giving me a display error and I don't understand why.
Can someone help please
__Sumarize1 = SUMMARIZE( VALUES('QuoteConvertionStartDate'), 'QuoteConvertionStartDate'[Date Table].[Day] )
This is the error:
Feedback Type:
Frown (Error)
Timestamp:
2019-10-24T14:17:20.5579571Z
Local Time:
2019-10-24T10:17:20.5579571-04:00
Session ID:
bd55acbd-6bfb-4845-8236-3d43a928c724
Release:
July 2019
Product Version:
2.71.5523.941 (19.07) (x64)
Error Message:
MdxScript(Model) (152, 48) Calculation error in measure 'QuoteConvertionStartDate'[__Sumarize1]: A table of multiple values was supplied where a single value was expected.
OS Version:
Microsoft Windows NT 10.0.14393.0 (x64 en-US)
CLR Version:
4.7 or later [Release Number = 461814]
Peak Virtual Memory:
34.6 GB
Private Memory:
582 MB
Peak Working Set:
647 MB
IE Version:
11.3300.14393.0
User ID:
40b18b39-f274-474c-9a74-f7c97b331593
Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Model Default Mode:
Import
Snapshot Trace Logs:
C:\Users\administrator.EXECUTIVECOACH\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot2004889293.zip
Performance Trace Logs:
C:\Users\administrator.EXECUTIVECOACH\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_showIncrementalRefreshPolicy
PBI_qnaLiveConnect
PBI_keyDrivers
PBI_userFavoriteResourcePackagesEnabled
Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_NewWebTableInference
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
DPI Scale:
100%
Supported Services:
Power BI
Formulas:
section Section1;
shared Quotations = let
Source = Sql.Database("server01", "PHCS"),
dbo_Quotations = Source{[Schema="dbo",Item="Quotations"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_Quotations,{"QuotationID", "Status", "DateCreated"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [DateCreated] <> null and [DateCreated] <> ""),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"DateCreated", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"DateCreated", Order.Descending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Status] <> null and [Status] <> "")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [DateCreated] > #date(2015, 1, 1)),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows2",{{"DateCreated", Order.Descending}})
in
#"Sorted Rows1";
shared QuotationMovements = let
Source = Sql.Database("server01", "PHCS"),
dbo_QuotationMovements = Source{[Schema="dbo",Item="QuotationMovements"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_QuotationMovements,{"QuotationID", "StartDateTime"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"StartDateTime", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"StartDateTime", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([StartDateTime] <> null)),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"StartDateTime", Order.Descending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows1", each [StartDateTime] > #date(2015, 1, 1))
in
#"Filtered Rows1";
shared Calendar = let
Source = Sql.Database("server01", "PHCS"),
dbo_Calendar = Source{[Schema="dbo",Item="Calendar"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_Calendar,{{"Date", type date}})
in
#"Changed Type";
shared QuoteConvertionStartDate = let
Source = Table.NestedJoin(Quotations, {"QuotationID"}, QuotationMovements, {"QuotationID"}, "QuotationMovements", JoinKind.LeftOuter),
#"Expanded QuotationMovements" = Table.ExpandTableColumn(Source, "QuotationMovements", {"QuotationID", "StartDateTime"}, {"QuotationMovements.QuotationID", "QuotationMovements.StartDateTime"}),
#"Sorted Rows" = Table.Sort(#"Expanded QuotationMovements",{{"QuotationMovements.StartDateTime", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([QuotationMovements.StartDateTime] <> null)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [QuotationMovements.StartDateTime] <> null and [QuotationMovements.StartDateTime] <> ""),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [QuotationMovements.StartDateTime] > #date(2015, 1, 1)),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each [QuotationMovements.QuotationID] <> null and [QuotationMovements.QuotationID] <> ""),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each [Status] <> null and [Status] <> ""),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each [QuotationMovements.QuotationID] <> null and [QuotationMovements.QuotationID] <> ""),
#"Filtered Rows6" = Table.SelectRows(#"Filtered Rows5", each [QuotationID] <> null and [QuotationID] <> ""),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows6", {"QuotationMovements.StartDateTime"}, Calendar, {"Date"}, "Calendar", JoinKind.LeftOuter),
#"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"Date"}, {"Calendar.Date"}),
#"Sorted Rows1" = Table.Sort(#"Expanded Calendar",{{"DateCreated", Order.Descending}})
in
#"Sorted Rows1";
Solved! Go to Solution.
the problem is that
the problem is that