SUMX DAX measure value loads blank after refresh in Service **Urgent**

[Anonymous]

Hi,

I have a measure that loads perfectly in desktop. However, once I publish the report to service, after a refresh of the page; the values appear as blank. 

What is the measure: Calculate sum of 2 columns in a table after applying some filters. 

Open_WIP =

        var t_date = max(Cal[Date])

           return SUMX(FILTER(WIP_All,

                 WIP_All[WipDate]<=t_date &&

                 or(WIP_All[Winvdate] > t_date, ISBLANK(WIP_All[Winvdate])) &&

                 WIP_All[Billable]="TRUE" &&

                 WIP_All[WCodeSer]<>blank() &&

                 WIP_All[Windicator]<>"P"),

        WIP_All[Actual $]+WIP_All[Wexp])

Glossary:

Max(Cal[Date]) is today's date in Michigan. This value is calculated using the third option proposed on RADCAD - Solving DAX Time Zone Issue in Power BI since Michigan has different dates for DST.

'Open_WIP' DAX is segmented by aging buckets and is used for visualization. Respective Dax Measure:

OpenWIP group =

CALCULATE([Open_WIP],

                   FILTER(WIP_All,

                              COUNTROWS(FILTER(Aging_Groups,

                                     WIP_All[Days_between]>=Aging_Groups[Min] && WIP_All[Days_between]<=Aging_Groups[Max]))>0))

WIP[Days_between] is a count of days between the date of a row & Max(Cal[Date]) represented as whole number.

Aging_Group table:

 

The measure & visuals works absolutely fine in desktop, but returns blank in service after a refresh. The first image below represents DAX measure values in desptop. Second image represents the blank values in service after refresh.

Matrix as a visual that uses 'Open_WIP group' as values; on desktop

<Blank> values in Service after upload & refresh

More info on version of application/softwares used:

Latest Power bI desktop used - August 2021

Latest Power bI Enterprise Gateway - June 2021

Please can you advise why this is happening? 

Best Regards,

Pooja

[Greg_Deckler]

@Anonymous First things first, try editing the report in the Service and make sure you still have data.

[Anonymous · Accepted solution]

@Greg_Deckler Thank you for the response. 

Yes, the data loads perfectly well in BI service. 

I may have identified the issue causing this trouble in Service.

This relates to the Filter WIP[Billable] = "TRUE"

I had transformed column 'Billable', having values 0, 1, representing 'No' & 'Yes' respectively, to Binary in Power Query. When loaded as a table in BI desktop; this column automatically changed the data type to 'Text' and thereby, BI service reads this as 0, -1 for 'FALSE', 'TRUE' respectively.

An issue that was raised in 2015 continues to persist in service as of 2021 - Community question raised in 2015 - True/False Filter is now 0/-1 ???

Applied fix- created a conditional column in query to calculate true/false text column again; Will share more info if this rectifies the issue

Data type inconsistency between power query & loaded table in desktop

True/False changed to -1 & 0 in BI service

[Anonymous]

The applied fix rectified the issue! 

However Power BI needs to be better at fixing bugs issues between the various platforms - Query editor -> table load in desktop -> data type chnage in service.