Dax working in App and Dev Workspace on the Service, but not in Test and Prod when deployed??

[Tihannah]

Why would dax used in a visual work fine on the desktop app, when published to the service in a dev workspace, but once deployed to Test or Prod workspaces give an error??

Our admin wants us only to publish to Dev, then deploy to Test and Prod. I just tested it and published the same dataset to Test instead of deploying from Dev and the error is not there?? This is the error:
An argument of function 'ROUND' has the wrong data type or the result is too large or too small.

I've tried fixing it, but Im working with a very large dataset, so the process is that I have to deploy and refresh the dataset, which can take up to an hour before I know whether the fix worked or not since it only shows up after its been deployed. The report shows no error until then??

[Tihannah · Accepted solution]

I was able to resolve just by removing the "ROUND" from the dax measure. Gave me the same result without the error. Thanks!

[Deku]

Assume you have different data in test and prod. This can be caused by a null, you could try updating the measure to handle nulls if( isblank( [number] ) , round(...))

[Tihannah]

It's the exact same data, except that it's published to Dev, and then deployed through a pipeline for Test and Prod. This is one of the dax giving the error:

FTE Stats YTD =

SWITCH(

    TRUE(),

    SELECTEDVALUE('KPIs'[Selection]) = "Total FTEs",

        FORMAT(ROUND(IF(ISNUMBER([Ttl FTEs YTD]), [Ttl FTEs QTD], 0)/1000, 1), "0.0K"),

    SELECTEDVALUE('KPIs'[Selection]) = "Revenue/FTE",

        FORMAT(ROUND(DIVIDE(TOTALYTD([Revenue/FTE], 'Calendar Dim'[Cal_Dt]), 1000, 0), 0) * 1000, "$#,##0,K"),

    SELECTEDVALUE('KPIs'[Selection]) = "Payroll/FTE",

        FORMAT(ROUND(IF(ISNUMBER([Payroll/FTE YTD]), [Payroll/FTE YTD], 0)/1000, 1), "$#,##0.0K"),

    BLANK()

)

[v-achippa]

Hi @Tihannah,

Thank you for reaching out to Microsoft Fabric Community.

Even though the dataset is same, the error looks like the input to ROUND() function becomes invalid after deployment. Power bi desktop and Dev might handle such scenarios during testing, but the service environments like Test/Prod can be more strict, especially after deployment. The issue here is likely due to a BLANK() or an unexpected value being passed into ROUND().

Please try updating the DAX measure like below:

FTE Stats YTD =

SWITCH(

    TRUE(),

    SELECTEDVALUE('KPIs'[Selection]) = "Total FTEs",

        FORMAT(ROUND(DIVIDE(COALESCE([Ttl FTEs QTD], 0), 1000),1), "0.0K"),

    SELECTEDVALUE('KPIs'[Selection]) = "Revenue/FTE",

        FORMAT(ROUND(DIVIDE(TOTALYTD([Revenue/FTE], 'Calendar Dim'[Cal_Dt]), 1000, 0),0) * 1000, "$#,##0,K"),

     SELECTEDVALUE('KPIs'[Selection]) = "Payroll/FTE",

        FORMAT(ROUND(DIVIDE(COALESCE([Payroll/FTE YTD], 0), 1000),1),"$#,##0.0K"),

    BLANK()

)

And also in the deployment pipeline, check both the dataset and report are being deployed, not just the report.

After deployment, refresh the dataset in Test and Prod workspaces.

Review any RLS roles that might behave differently across environments.

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 and regards,

Anjan Kumar Chippa