cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TiaCamilian
Resolver I
Resolver I

Summarize a date table

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";

1 ACCEPTED SOLUTION
TiaCamilian
Resolver I
Resolver I

the problem is that

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

View solution in original post

1 REPLY 1
TiaCamilian
Resolver I
Resolver I

the problem is that

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors