Forum Discussion

cath1ynn's avatar
cath1ynn
Resolver II
1 year ago
Solved

Cannot force a semantic dataset to fail

I have a semantic dataset that reads data from a couple of dataflows upstream. These dataflows have a field called "LAST_REFRESH_DATETIME" that contains the timestamp when the dataflow was last refreshed.

 

I wanted to check that the "LAST_REFRESH_DATETIME" value was today and if it's not, to force the semantic dataset refresh to fail.

 

I have tried a few different methods where when I tested it on Power BI Desktop, it would fail immediately but when refreshing in Power BI Service, it would either completed successfully or just hang.

 

First method: Add a new column that checks if the "LAST_REFRESH_DATETIME" is >= TODAY(), if not, error. This method fails in Power BI Desktop with row level errors but ran successfully in Power BI Service 🤔

 

Second method: Validate the "LAST_REFRESH_DATETIME" date values and sum up the error flags. If the sum is greater than 0 then force the entire query to error out. This method fails to load the query in Power BI Desktop and hangs indefinitely in Power BI Service. I've tried waiting for over 20 mins and it just remain in In Progress status. I've then tried to go to the dataflow upstream and change the "LAST_REFRESH_DATETIME" value back into range and then lo and behold, the semantic dataset refresh that was hanging then completes within a min.

 

Has anyone encounter this issue, and if yes, have you found a way to force Power BI Service to fail a data refresh? From what I can tell based on the second method, it looks like Power BI Service is deferring the query evaluation when it encounter the error and seems to be just waiting for the upstream dataflow's issue to fix itself, though I'm not sure how often it's retrying the data refresh. This is not running in Fabric yet and I wouldn't want to move this to Fabric if this is going to be the behaviour!

Need Help
  • cath1ynn's avatar
    cath1ynn
    1 year ago

    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.

5 Replies

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    1 year ago

    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.

    • cath1ynn's avatar
      cath1ynn
      Resolver II
      1 year ago

      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:

      • v-sathmakuri's avatar
        v-sathmakuri
        Community Support
        1 year ago

        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.