Summarize a date table

TiaCamilian — Thu, 24 Oct 2019 14:23:58 GMT

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}})

#"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))

#"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}})

#"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}})

#"Sorted Rows1";

Re: Summarize a date table

TiaCamilian — Thu, 24 Oct 2019 18:08:39 GMT

the problem is that

VALUES('QuoteConvertionStartDate') returns a table and not a single value

topic Summarize a date table in DAX Commands and Tips

Summarize a date table

Re: Summarize a date table