Forum Discussion
Cannot force a semantic dataset to fail
Looks like it was just a transient issue on that day. The dataset is now failing correctly in Power BI Service with no change in the script.
Hi cath1ynn ,
Thank you for reaching out to Microsoft Fabric Community.
I have created a validation column and thrown error if the count is greater than 0. Below is working in both power bi desktop and power bi service. Please find below screenshot for reference. Also attached the pbix file for reference.
let
Source = Sql.Database(server, databasename),
dbo_vwcustomer = Source{[Schema="dbo",Item="vwcustomer"]}[Data],
InvalidDates = List.Select(dbo_vwcustomer[csutomerdate], each Date.From(_) <> Date.From(DateTime.LocalNow())),
ThrowError = if List.Count(InvalidDates) > 0 then error "Dataflow not refreshed today" else dbo_vwcustomer
in
ThrowError
Screenshot from service:
Screenshot from desktop:
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks & Regards,
Rekha,
CustomerSupportTeam.
Hi v-sathmakuri
Out of curiosity, how many records are in your view? I noticed for your test results in Power BI Service, the successful run was almost instant, yet the failed run took about 8 mins to fail. My table in this example has about 2000 records (5 years worth of dates and date dims) and I've waited over 30 mins and it still didn't fail so I had to cancel the refresh.
Here's the M script that I'm testing with:
let
Source = PowerBI.Dataflows(null),
...
GDD1 = LastStep{[entity="GLOBAL DATE DIMENSION v2"]}[Data],
RemovedOtherColumns = Table.SelectColumns(GDD1,
{"DATE", "CYYYY", "MMMM", "MMM", "DDDD", "DDD", "CYYYY-MMM", "DATE_FY", "DATE_FY_FULL", "DATE_CQTR_NUM", "DATE_FQTR_NUM", "DATE_FQ", "DATE_CM", "DATE_FM", "IS_BUSINESS_DAY", "DATE_SORT", "BUSINESS_DAY_SORT", "DateID", "LAST_REFRESH_DATETIME"
}),
// Capture the current UTC date once to ensure consistency
CurrentUTC = DateTimeZone.UtcNow(),
Today = Date.From(CurrentUTC),
// Extract distinct dates for validation
DateList = Table.Column(RemovedOtherColumns, "LAST_REFRESH_DATETIME"),
UniqueDates = List.Distinct(List.Transform(DateList, each Date.From(_))),
// Check for any date not equal to Today; returns list of errors (if any)
ValidationErrors = List.Select(UniqueDates, each _ <> Today),
// Test Method 1: Create a scalar result that “asserts” validity.
// If there are validation errors, this value becomes an error.
FinalTable = if List.Count(ValidationErrors) > 0 then
error "Validation failed: Some rows do not have today's date."
else
RemovedOtherColumns
// Test Method 2: Force evaluation by adding the scalar to our final output.
// FinalTable = Table.AddColumn(RemovedOtherColumns, "ValidationResult", each AssertValidDates, type logical)
in
FinalTable
And here it is failing correctly in Desktop:
Hi cath1ynn ,
My source contains only two rows, yet it's taking a while before the error appears. However, the error occurs in both Power BI Service and Desktop.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks & Regards,
Rekha,
CustomerSupportTeam.