Calculation showing as NULL

Amy Jandreau 61 Reputation points
2022-04-07T20:11:20.773+00:00

Hi All,
In my cube I have 2 measures which are count distinct. I created a calculation (Measure1-Measure2), when the values of Measures are equal instead of showing the difference as 0 (ZERO) , my calculation is showing it as NULL in the browser. I have tried different formats and none did not work. Please advice.
Thanks

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 34,581 Reputation points
    2022-04-08T01:25:44.503+00:00

    Hi @Amy Jandreau ,

    Are you using SSAS Tabular or SSAS Multidimensional?

    If you are using Tabular, you may try add 0 in your measure to display 0, something like Mymeasure = Mymeasure+0

    If you are using SSAS Multidimensional, you may need to use IIF to display the value you want, IIF(Measure1=Measure2, "0", Measure1=Measure2)

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. BK 6 Reputation points
    2023-01-16T14:00:09.6533333+00:00

    @ZoeHui-MSFT and @Darren Gosbell Thanks that worked.

    1 person found this answer helpful.
    0 comments No comments