Dax Summary Table has an error , despite no changes to it for 1 month

[Pandadev]

I have a published report , that pulls in multiple tables from SQL server.

Each table has measures added so i can get a count of rows based on the user name.

I then created a dax summary table , where I could add all measures , so there was an end report showing each table name , how many rows were assigned to each user..

This has been published and has been updating every day for the past month , via a personal gateway.

Then yesterday , it updated with no errors , but the summary table was showing no data , just errors.

So I opened the desktop version and tried to refresh it , but all the summary tables are showing as in error.

This expression refers to a relationship object named 9cf1b207-94c2-4b52-8641-7ac4024fc1d4 which has an error.

It took me 3 months to write this report , so I am very worried and just cant find a solution as why this error has suddenly occurred.

I have tried uninstalling and reinstalling PowerBI and the Gateway , but still no joy. Any help on this and why it has happened would be much appreciated

[mahoneypat]

First make sure you do a Save As of your pbix file, so you can always get back to the current state.  I suspect that you had new data show up that is either a duplicate of a previous values (and that DAX table has a 1:1 or 1:Many relationship with something else), or the new data is of a different datatype and threw an error in your DAX expression.  You could try adding a Date filter to your queries to when you know it was working and refresh and/or check for duplicates and/or data type errors.

Regards,

Pat

[Pandadev]

I located the issue , it was where I was creating a dax summarised table from 4 tables I had imported from SQL , all of those tables contained no data . As the report is a QC Checker , eventually there will be more instances where the SQL codes will bring in empty tables , is there a way to prevent an error if the dax summarised table . as at present any error will cause the whole report to fail. as the report is built around the main summarised table ,

[mahoneypat]

You could use two variables and an IF expression in your DAX table.  If COUNTROWS of the table ISBLANK, then load some dummy table you provide in the expression (or a dummy table you make in the query editor).

Regards,

Pat

[Pandadev]

Sound like a good idea , how would I wrire that please. Getting desperate as this report took me 3 months to create

[Anonymous]

@Pandadev - there could have been some changes in the SQL server database in terms of column/table.

You may to take a look over that.