Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors