Cannot convert value of type text to true/false

[Anonymous]

I have the below measure:

XIRR = CALCULATE(XIRR('TABLEA','TABLEA'[ColumnA], 'TABLEA'[Date].[Date]), 'TABLEA'[Filter])

This flags the error:

MdxScript(Model) (3, 97) Calculation error in measure 'TABLEA'[XIRR]: Cannot convert value 'ABC' of type Text to type True/False.

How can I use the XIRR function on dynamic values as the below seems to work. Should I be using a GROUP BY or SUMMARIZE?

XIRR = CALCULATE(XIRR('TABLEA','TABLEA'[ColumnA], 'TABLEA'[Date].[Date]), 'TABLEA'[Filter] = "ABC")

Thanks.

[SteveCampbell]

Using 'TABLEA'[Filter] as a filter will not work, as it will try and evaluate this, but it is meaningless. It is asking what you want to filter by, and it is given a column name with no context. I'm unsure what you're trying to achieve. If you are filtering, you should be able to directly use TABLEA[Filter] as a filter in your report, and omit it from the measure?

Please elaborate if I have misunderstood.

[Anonymous]

Hi,

Thanks for your reply.

If I don't use the filter within the measure I get the same XIRR result value for all rows, even although 'TABLEA'[ColumnA]' is different for each row, its as if the XIRR function is summing the ColumnA value for all rows and then performing the function logic. This is why I need the function to run on a row by row basis or group by distinct ColumnA values before calculating the function.

Thanks.

[v-huizhn-msft]

Hi @Anonymous,

Do you mind share your sample table for further analysis? So that we can check the specific reason why the error returned? Thanks for understanding.

Best Regards,
Angelia

[Anonymous]

Hi,

Below is my "Data" table of data...

Investment    Cashflow        Date

ABC	-57.28775201	30/06/2017
ABC	-1.017111279	31/12/2017
ABC	-1.011349925	30/09/2017
ABC	-0.942991769	31/03/2018
ABC	0.43541108	30/06/2018
ABC	0.445494726	30/09/2018
ABCD	0.456578729	31/12/2018
ABCD	0.497459863	30/06/2019
ABCD	0.507532876	30/09/2019
ABCD	0.507597629	31/12/2021
ABCD	0.512661415	31/12/2019
ABCD	0.513519403	30/09/2021
ABCD	0.520365843	30/06/2021
ABCDE	0.521142079	31/03/2020
ABCDE	0.567614866	31/03/2021
ABCDE	0.56995523	31/12/2020
ABCDE	0.577998285	30/09/2020
ABCDE	0.587201729	30/06/2020
ABCDE	8.175973867	31/03/2022
ABCDE	9.301638451	31/03/2019
ABCDE	69.61181073	30/06/2022

I then created a new "XIRR Col" from the fields list in Power BI:

XIRR Col = XIRR('Data','Data'[Cashflow], 'Data'[Date].[Date])

I seem to get the same value for each of my rows in the XIRR Col???

Thanks.

[v-huizhn-msft]

Hi @Anonymous,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Anonymous,

You want to calculate the XIRR value for "ABC", "ABCD", "ABCDE" seperately, right? But XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value. For all Cashflow rows for "ABCD" and "ABCDE", there is no negative value, it returns error. 

Thanks,
Angelia

[magsod]

Instead of creating a new column you can directly do this calculation in a new measure instead.

Then you can use that measure in for example a table visual with your investments as rows in that table?

XIRR Measure = XIRR('Data','Data'[Cashflow], 'Data'[Date].[Date])

Br,

Magnus