Forum Discussion

SaraFtg's avatar
SaraFtg
Frequent Visitor
5 years ago
Solved

Calculated column changes value in report

Hi,   First, English is not my mother tounge, so I hope I make myself understood :).   I'm quite new att Power BI and I'm struggeling with a calculated column and the outcoming result in my repor...
Need Help
  • SaraFtg's avatar
    SaraFtg
    5 years ago

    Hi selimovd,

     

    After some more digging I found that empty cells in date column in my Customer table returned 30/12/1899, so when I calculated MemberYear the result became 120 years.

     

    Instead of IF(ISBLANK(vContractMemberFrom),-1,... I tried IF(vContractMemberFrom = 0,-1,... and now it seems to work fine.

     
    Thanks for taking your time to respond to my issue!
selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey SaraFtg ,

 

can you check if Document No 765663 has multiple rows in your data?

If you chose MAX as aggregation for MemberYears (group) it could be that it would show the MAX of the rows and if that Document No has multiple rows that would explain it.

 

Also try to set the aggregation in the visual to "Don't summarize":

 

Like this all rows should be shown for sure.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
  • SaraFtg's avatar
    SaraFtg
    Frequent Visitor
    5 years ago

    Hi selimovd,

     

    Thanks for you reply!

     

    Document No appears in three different tables, and on several rows in two of them, but not in the table where this calculation is made. 

     

    The aggregation is set to Don't summarize, these are the options I have

     

     

    Best regards,

    Sara

     

  • SaraFtg's avatar
    SaraFtg
    Frequent Visitor
    5 years ago

    Hm, when adding the MembertYear-column (also set to Don't summarize) to my visual the problem seems to be that -1 is set to 120 (resulting in that it's grouped as "50+"). So - question is why that is happening?

     

     

    • selimovd's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      Hey SaraFtg ,

       

      can you share the file? It would be a lot easier to take a look at the file itself.

       

      Best regards

      Denis

      • SaraFtg's avatar
        SaraFtg
        Frequent Visitor
        5 years ago

        Hi selimovd,

         

        After some more digging I found that empty cells in date column in my Customer table returned 30/12/1899, so when I calculated MemberYear the result became 120 years.

         

        Instead of IF(ISBLANK(vContractMemberFrom),-1,... I tried IF(vContractMemberFrom = 0,-1,... and now it seems to work fine.

         
        Thanks for taking your time to respond to my issue!