Couldn't Load Data For This Visual (The reference calculated table which does not hold any data... )

[SuddenClarity]

I published a dashboard to the Service, which contains a slicer that allows the user to filter the data by one of the numerical sales measures. In order to achieve this, I had to write a series of DAX measures as I believe you cannot simply insert a numerical measure in a slicer visual. 

I have set the dashboard to refresh every morning, but I seem to get this error related to the slicer: 

The table mentioned in the above error message was created using the table function GENERATESERIES as per the formula below:

VarFilter = GENERATESERIES(0, [SalesVar], 1000)

This calculated table is essentially a single column that contains every single possible [SalesVar] value in increments of $1,000. One thing to note is that the actual [SalesVar] amount can be negative or postive. After I created this table, I place it into a slicer.

Then on the visuals that I want to filter using this slicer I created a DAX measure, called "VisualFilter", which returns 1 or 0 based on whether the absolute [SalesVar] value is between the range indicated int he slicer. I then placed this "VisualFilter" into the visual-level filter and set it to equal 1. 

VisualFilter =

 VAR MinValue = MIN('VarFilter'[Value])

 VAR MaxValue = MAX('VarFilter'[Value])

 VAR CurrentValue = ABS([SalesVar])

RETURN

IF( CurrentValue >= MinValue && CurrentValue <= MaxValue, 1, 0 )

In the above measure, I had to wrap [SalesVar] in the ABS function becuase the value can be either postive or negative. And I am only interested in whether the absolute value of [SalesVar] has met the condition above. 

Lastly, to ensure that the slicer only shows the range of data currently displayed in the visuals, I created another DAX measure to filter this slicer by, called SlicerFilter:

SlicerFilter =

VAR TempTable =

SUMMARIZE(

    'Customer',

    'Customer'[Customer Name],

    "SalesVariance", [SalesVar]

)

VAR MinValue = MINX(TempTable,ABS([SalesVar]))

VAR MaxValue = MAXX(TempTable,ABS([SalesVar]))

VAR CurrentSlicerValue = IF ( HASONEVALUE(VarFilter[Value]), VALUES(VarFilter[Value]) )

RETURN

IF(

    CurrentSlicerValue >= MinValue && CurrentSlicerValue <= MaxValue,

    1,

    0

)

So essentially, the above measure first creates a temporary table grouping the sales value by each customer (because this is what the other visuals do), and then taking the min and max value of those groupings. Then the condition in the RETURN statement produces a 1 if any given value in the calculated table is within that min and max range. And again, I wrap the ABS function [SalesVar] because that value can either be postive or negative but I am only interested in the absolulte value. 

So with all said and done, I am getting the above error message every morning. And I am wondering if it has to do with the GENERATESERIES table function and how I am generating the slicer values. Unfortunately I cannot create this table in PQ as I need it to be dynamic based on the sales that get updated every morning. I am also wondering if its the ABS functions I'm using to account for the negative and postive sales figures.. perhaps I am using them incorrectly? 

[v-yinliw-msft]

Hi @SuddenClarity ,

For GENERATESERIES function,

Your DAX code : VarFilter = GENERATESERIES(0, [SalesVar], 1000)  use the [SalesVar] for the second parameter may have some errors. Because when [SalesVar] < 0, the result will be blank.

About the GENERATESERIES function, you can read the documentation below: GENERATESERIES function - DAX | Microsoft Learn

Or You are trying to use two or more fields from tables that are not related. You need to remove the unrelated fields from the visual and then create a relationship between the tables. Once you have done this change, you can add the fields back to the visual. This can be done in Power BI Desktop or Power Pivot for Excel. Troubleshooting tile errors - Power BI | Microsoft Learn

Could you please provide sample data for testing? Sensitive information can be removed in advance.

What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SuddenClarity]

So the weird part that I do not get any errors on the Desktop version and when I first publish to the service there are no errors either. It is only the following morning after the refresh does the error show up. 

With GENERATESERIES, I didn't get any 0 in my calculated table. I thought that if I use [SalesVar] as the end value in GENERATESERIES it would simply just use the sum of that measure as my end value? For e.g. if I plug in [SalesVar] and check the table it goes from 0 all the way to $75,000,000. Which is a rediculously high number, so I assumed it was just summing all possible [SalesVar] numbers. 

[v-yinliw-msft · Accepted solution]

Hi @SuddenClarity ,

This issue has been confirmed as a known issue internally.

Internal ICM ID:335828244

Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.