Error on report

[Anonymous]

Hi,

I hope i have posted this correctly and in the right place. I had a report that worked perfectly for 2 years and now broken. Any help appreciated.

Feedback Type:
Frown (Error)

Timestamp:
2021-05-20T12:09:09.8568895Z

Local Time:
2021-05-20T13:09:09.8568895+01:00

Session ID:
d5410b31-f5ca-4373-9eb8-6d7a189a76c5

Release:
May 2021

Product Version:
2.93.641.0 (21.05) (x64)

Error Message:
MdxScript(Model) (48, 9) Calculation error in measure 'Dates'[No of households in TA at MonthEnd]: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

OS Version:
Microsoft Windows NT 10.0.18363.0 (x64 en-GB)

CLR Version:
4.7 or later [Release Number = 528040]

Peak Virtual Memory:
39.4 GB

Private Memory:
1.02 GB

Peak Working Set:
1.27 GB

IE Version:
11.1411.18362.0

User ID:
0a5417f5-ed94-483c-a158-502ffeb60af3

Workbook Package Info:
1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\men_mcguirep\Microsoft\Power BI Desktop Store App\FrownSnapShotc9dbc135-bbac-435e-9565-074942024198.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\men_mcguirep\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_JsonTableInference
PBI_NewWebTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_eimInformationProtectionForDesktop
PBI_cartesianMultiplesAuthoring

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

Formulas:

section Section1;

shared Sheet1 = let
Source = Excel.Workbook(File.Contents("C:\Users\men_mcguirep\Desktop\Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers2" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type5" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Case ID", Int64.Type}, {"UAI", type text}, {"Salutation", type text}, {"Case Type", type text}, {"Case Status", type text}, {"Time in Current TA Tenancy", type text}, {"Bedroom need", Int64.Type}, {"Household makeup", type text}, {"Total Time in TA", type text}, {"Ethnic group of the main applicant", type text}, {"Nationality of the main applicant", type text}, {"Number of dependent children", Int64.Type}, {"Homefinder Somerset Number", Int64.Type}, {"Case Officer", type text}, {"Case Opened Date", type date}, {"Advice only case", type text}, {"Accommodation at time of application", type text}, {"Employment status of the main applicant", type text}, {"Date homeless application made", type date}, {"Type of assessment?", type text}, {"Assessment date?", type date}, {"Assessment of applicants overall financial situation", type text}, {"Benefits towards housing costs", type text}, {"Benefits towards other living costs", type text}, {"Referrals into the Authority", type text}, {"Date of assessment of circumstances and needs", type date}, {"Assessment of circumstances and needs", type text}, {"Date Prevention Duty started", type date}, {"Prevention Activity", type text}, {"Reason Prevention Duty ended", type text}, {"Temporary accommodation provided or duty owed (Prevent)", type text}, {"Date Relief Duty started", type date}, {"Relief Activity", type text}, {"Reason Relief Duty ended", type text}, {"Temporary accommodation provided or duty owed (Relief)", type text}, {"Date Prevention Duty ended", type date}, {"Date Relief Duty ended", type date}, {"Main duty decision date", type date}, {"Outcome of decision", type text}, {"Priority need decision", type text}, {"Reason the section 193(2) Duty, the section 193C(4) Duty or the", type any}, {"Section 193(2) or section 193c(4) duty end date", type any}, {"Accommodation outcome (Final Duties)", type any}, {"Reason to believe PN", type text}, {"Patch", type text}, {"Accommodation Outcome (Prevent)", type text}, {"Accommodation outcome (Relief)", type text}, {"Case Closed Date", type date}, {"PHP has been updated by the client", type text}, {"Reason Advice Only ended", type text}, {"Date triage completed", type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type5",{{"Case ID", Int64.Type}, {"UAI", type text}, {"Salutation", type text}, {"Case Type", type text}, {"Case Status", type text}, {"Time in Current TA Tenancy", type text}, {"Bedroom need", Int64.Type}, {"Household makeup", type text}, {"Total Time in TA", type text}, {"Ethnic group of the main applicant", type text}, {"Nationality of the main applicant", type text}, {"Number of dependent children", Int64.Type}, {"Case Officer", type text}, {"Case Opened Date", type date}, {"Advice only case", type text}, {"Accommodation at time of application", type text}, {"Employment status of the main applicant", type text}, {"Date homeless application made", type date}, {"Type of assessment?", type text}, {"Assessment date?", type date}, {"Assessment of applicants overall financial situation", type text}, {"Benefits towards housing costs", type text}, {"Benefits towards other living costs", type text}, {"Referrals into the Authority", type text}, {"Date of assessment of circumstances and needs", type date}, {"Assessment of circumstances and needs", type text}, {"Date Prevention Duty started", type date}, {"Prevention Activity", type text}, {"Reason Prevention Duty ended", type text}, {"Temporary accommodation provided or duty owed (Prevent)", type text}, {"Date Relief Duty started", type date}, {"Relief Activity", type text}, {"Reason Relief Duty ended", type text}, {"Temporary accommodation provided or duty owed (Relief)", type text}, {"Date Prevention Duty ended", type date}, {"Date Relief Duty ended", type date}, {"Main duty decision date", type date}, {"Outcome of decision", type text}, {"Priority need decision", type text}, {"Reason the section 193(2) Duty, the section 193C(4) Duty or the", type any}, {"Section 193(2) or section 193c(4) duty end date", type any}, {"Accommodation outcome (Final Duties)", type any}, {"Reason to believe PN", type text}, {"Patch", type text}, {"Accommodation Outcome (Prevent)", type text}, {"Accommodation outcome (Relief)", type text}, {"Case Closed Date", type date}, {"PHP has been updated by the client", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Case Opened Date", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Time between triage and assessment booked", each [#"Assessment date?"]-[Date triage completed]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "time between open and actual assessment ", each [Date of assessment of circumstances and needs]-[Case Opened Date]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "No of days in prevention", each [Date Prevention Duty ended]-[Date Prevention Duty started]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "No of days in relief", each [Date Relief Duty ended]-[Date Relief Duty started]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"No of days in prevention", Int64.Type}, {"No of days in relief", Int64.Type}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type2", "No of days for decision after relief", each [Main duty decision date]-[Date Relief Duty ended]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom4",{{"No of days for decision after relief", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Advice only case", "Type"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","Yes","Advice Only",Replacer.ReplaceText,{"Type"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","No","Homeless Application",Replacer.ReplaceText,{"Type"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Salutation"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date triage completed", type date}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type1","Secured existing accommodation for 6 months","Prevented: Existing",Replacer.ReplaceText,{"Reason Prevention Duty ended"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Secured alternative accommodation for 6 months","Prevented: Alternative",Replacer.ReplaceText,{"Reason Prevention Duty ended"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Secured existing accommodation for 12 or more months","Prevented: Existing",Replacer.ReplaceText,{"Reason Prevention Duty ended"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Secured alternative accommodation for 12 or more months","Prevented: Alternative",Replacer.ReplaceText,{"Reason Prevention Duty ended"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Registered Provider tenancy","Social rented sector",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Private rented sector: self-contained","Private rented sector",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Private rented sector: lodging (not with family or friends)","Lodging",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Council tenancy","Social Rented Sector",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","No fixed abode: rough sleeping","Rough sleeping",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Social rented supported housing or hostel","Supported Housing",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","No fixed abode: rough sleeping","Rough sleeping",Replacer.ReplaceText,{"Accommodation outcome (Relief)"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value12","No fixed abode: not rough sleeping","No fixed abode",Replacer.ReplaceText,{"Accommodation outcome (Relief)"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","Registered Provider tenancy","Social rented sector",Replacer.ReplaceText,{"Accommodation outcome (Relief)"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value15","Social rented supported housing or hostel","Supported housing",Replacer.ReplaceText,{"Accommodation outcome (Relief)"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value13","Private rented sector: self-contained","Private rented sector",Replacer.ReplaceText,{"Accommodation outcome (Relief)"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","Private rented sector: HMO","HMO",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)", "Accommodation outcome (Relief)"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","No fixed abode: not rough sleeping","No fixed abode",Replacer.ReplaceText,{"Accommodation Outcome (Prevent)", "Accommodation outcome (Relief)"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","Secured accommodation for 6 months","Relieved: Alternative",Replacer.ReplaceText,{"Reason Relief Duty ended"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","Secured accommodation for 12 months","Relieved: Alternative",Replacer.ReplaceText,{"Reason Relief Duty ended"})
in
#"Replaced Value20";

shared TA = let
Source = Excel.Workbook(File.Contents("C:\Users\men_mcguirep\Desktop\Data.xlsx"), null, true),
TA_Sheet = Source{[Item="TA",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(TA_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers";

[Anonymous]

Strange this has now been sorted. One of the columns on the data source was slightly wrong and caused the issue. 

[v-lili6-msft]

hi

from the error message:

MdxScript(Model) (48, 9) Calculation error in measure 'Dates'[No of households in TA at MonthEnd]: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

it should be something wrong with the measure  'Dates'[No of households in TA at MonthEnd] as above message, please check and adjust this message.

if still have the problem, please share your sample pbix file for us have a test, that will be a great help.

Regards,

Lin