Measures go blank after refresh in service but show on desktop

[Fuel_UK]

As described. I have a model with 2 tables. Its an accounting model.

We have measures that are calculating ageing brackets. 30day/60day and so on.

An example of the 30 day dax formula:

CR - 30 Days_Table = CALCULATE (

SUMX (

'JOURNAL (New)',

'JOURNAL (New)'[AmtLed]

),

FILTER (

'GACCDUDATE (New)',

'GACCDUDATE (New)'[CredInt] = 1

&& 'GACCDUDATE (New)'[BPRTYP_0] = 2

),

FILTER (

'JOURNAL (New)',

'JOURNAL (New)'[NumCalDate] <= [VcalAge30]

&& 'JOURNAL (New)'[NumCalDate] > [VcalAge60]

),

FILTER (

'JOURNAL (New)',

'JOURNAL (New)'[Cal YearPeriod]

<= MAXX (

'JOURNAL (New)',

'JOURNAL (New)'[Cal YearPeriod]

)

&& 'JOURNAL (New)'[Fiscal Period]

<= MAXX (

'JOURNAL (New)',

'JOURNAL (New)'[Fiscal Period]

)

&& 'JOURNAL (New)'[CalMonthIndex]

<= MAXX (

'JOURNAL (New)',

'JOURNAL (New)'[CalMonthIndex]

)

)

)

I have read a few cases in regard to Boolean failure based on 0 instead of True and visa versa but we have no filters on any pages. 

In desktop, all works well:

But online it is blank after refresh. The publish works until such time a refresh takes place (be it manual or scheduled).

Any help would be greatly appreciated as i have been stuck on this for weeks now.

Thanks!!

[PowerBIUser7854]

Hi there, is there any update on that one?

[Fuel_UK]

Hi, 

This was resolved by removing the custom select step in our direct query. So it has been resolved, no further updates.

Thanks!

[v-deddai1-msft]

Hi @Fuel_UK ，

First, please simplify your formula, you don't need to use maxx and sumx:

CR - 30 Days_Table =
CALCULATE (
    SUM ( 'JOURNAL (New)'[AmtLed] ),
    FILTER (
        'GACCDUDATE (New)',
        'GACCDUDATE (New)'[CredInt] = 1
            && 'GACCDUDATE (New)'[BPRTYP_0] = 2
    ),
    FILTER (
        'JOURNAL (New)',
        'JOURNAL (New)'[NumCalDate] <= [VcalAge30]
            && 'JOURNAL (New)'[NumCalDate] > [VcalAge60]
    ),
    FILTER (
        'JOURNAL (New)',
        'JOURNAL (New)'[Cal YearPeriod] <= MAX ( 'JOURNAL (New)'[Cal YearPeriod] )
            && 'JOURNAL (New)'[Fiscal Period] <= MAX ( 'JOURNAL (New)'[Fiscal Period] )
            && 'JOURNAL (New)'[CalMonthIndex] <= MAX ( 'JOURNAL (New)'[CalMonthIndex] )
    )
)

And I have test refresh by your sample pbix and excel for data source. It works well, did you see any error in your refresh history?

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

Best Regards,

Dedmon Dai

[Fuel_UK · Accepted solution]

Hi Dedmon, 

The above helped from a SE perspective so thanks for that, but it did not solve our issue. 

Turns out it was a custom select that we had in place. 

We replaced this with a standard connection to table in the SQL and it worked.

So seems the problem was with the custom select function in our direct query step. 

Thanks,

Roque

[mhossain]

Hello @Fuel_UK 

Can you upload/share here the pbix with your measures and dummy data?

As you have already mentioned the boolean thing, and it is known issue, but you are saying no page level filter.

[Fuel_UK]

Hi @mhossain , thank you for your prompt reply. 

File & Dummy Data 

See also an image of the actual with no filter showing in pane.

Thanks again, 

Roq

[Fuel_UK]

Also some additional notes. 

The actual report is connected via import mode to a SQL server. 

We then have a gateway setup on the server side and we have scheduled refreshes taking place.

The gateway is up and running no issues.

When we publish we see the dash no issues. But after pressing refresh, we get below:

Thanks again.

Roq

[mhossain]

Hi @Fuel_UK 

Checked at my end, all seems good, even schedule refresh/manual refresh online working fine with the datasets you shared.

If you have not updated the latest gateway version on your server, please try to do so.

Please lets see if anyone else share their viewpoints here.

[jonathan_a]

Hi, 

We are having the same issue, we have a measure in place in desktop where we look at the data in the past, Q3 & Q4 2021 , by removing the current filters on year and month. And all works fine in the desktop, and when uploading it to the service it works, until an automatic refresh happens, then it suddenly gives blanks. 

Did you come up with any solutions?

Formula:

RLM HL LY Q3Q4 = CALCULATE(SUM(VOLUME[[Measures]].[Invoice Quantity Hl]]]),ALL('Dim Time'),'Dim Customer'[Sales_Office__c] ="Direct Horeca (BE10)" ,User[Name]<>BLANK(), 'Dim Time'[QuarterInCalendar]= "Q3 2021" || 'Dim Time'[QuarterInCalendar]= "Q4 2021")

Table in desktop:

Table in service after scheduled refresh:

Any help is appreciated because ive tried everything i could think of..

[Fuel_UK]

HI there, 

So turns out when I had this issue, that the problem was in the select statement made at connection to SQL, nothing to do with DAX code on the measure itself. So, question is, how do you connect to your source data? If it is by using a custom select statement, then i would suggest you move away from this and rather direct query the dbo. Hope this makes sense. If this is not a problem from your connection, then i think we may be facing different challenges. Let me know. Good luck.

[jonathan_a]

Hi,

Thanks for the quick response.

We connect to our datacubes via MDX query, SF import and for teh date table we use a script , but we have it this way in various reports and there everything works.

It is suddenly and only with this report.. and i cannot find the issue.

Kind regards

Jonathan

[Fuel_UK]

Hi Jonathan, 

Sorry i couldnt be of any help. With my issue, i had a custom select query when connecting to our SQL DB. By removing this custom select query and connecting directly to a table in the dbo, it fixed this problem. Not sure if that guides you in any way but thats how we resolved our issue, taking out the custom select step on the data import. Sorry i couldnt be of more use. I would suggest you open a case with as much detail, including connection sources and methods etc, this community has solved my issues many many times. Best of luck. Roque

[jonathan_a]

Ah that is unfortunate, but ok thank you for the advise!

I have opened a case first already with microsoft themselves so we will see what they come up with and if they come up empty then ill make one.