Calculated column changes value in report

[SaraFtg]

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 report.

So, my company want's to follow up on how "old" a member is when they pay there membership fee (number of years as a member), and I have one column that calculates that:

MemberYears =

Var vContractMemberFrom = RELATED(Customer[Contract Member From])

Var vMinPeriodStartingDate = MINX(RELATEDTABLE(Sales), Sales[Member Period Starting Date])

Var vMemberYears =

IF(

ISBLANK(vContractMemberFrom),

-1,

ROUNDDOWN(YEARFRAC(vContractMemberFrom, vMinPeriodStartingDate, 1), 0)

)

RETURN

IF(NOT ISBLANK(vMinPeriodStartingDate), vMemberYears)

Why we have both "Missing" and "(Blank)" is because an invoice may refer to either a membership fee or other sales. 

Second, I'm grouping these values with SWITCH in an other column:

MemberYears (group) =

SWITCH(

TRUE(),

'Sales Headers'[MemberYears] = -1, "Missing",

ISBLANK('Sales Headers'[MemberYears]), "(Blank)",

'Sales Headers'[MemberYears] = 0, "0",

'Sales Headers'[MemberYears] = 1, "1",

'Sales Headers'[MemberYears] = 2, "2",

'Sales Headers'[MemberYears] = 3, "3",

'Sales Headers'[MemberYears] = 4, "4",

'Sales Headers'[MemberYears] = 5, "5",

'Sales Headers'[MemberYears] >= 6 && 'Sales Headers'[MemberYears] < 11, "6-10",

'Sales Headers'[MemberYears] >= 11 && 'Sales Headers'[MemberYears] < 21, "11-20",

'Sales Headers'[MemberYears] >= 21 && 'Sales Headers'[MemberYears] < 51, "21-50",

'Sales Headers'[MemberYears] >= 51 && 'Sales Headers'[MemberYears] < 1000, "50+"

)

I get the expected result in my datamodel

but in my report, all the items with value "-1", that should be grouped as "Missing", is converted to "50+" Why is that?

I've tried to add && <> -1 on the last row, but that doesn't work either. How is it possible that the datamodel and the report shows different values? Any one who has an idea or solution?

Thanks in advance!

[v-henryk-mstf]

Hi @SaraFtg ,

After reviewing your question, it seems that it has been resolved. You can mark the correct response as the standard answer to help the other members find it more quickly.

If there is still confusion, you can describe your problem in detail and provide relevant data and information. I will answer you as soon as possible.

Best Regards,
Henry

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[SaraFtg]

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]

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 · Accepted solution]

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!

[SaraFtg]

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