Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I want to create a column directly in BI but i keep getting errors. ive tried multiple options like the below:
can anyone help please?
In Excel the statement would be
=IF(Z3="Status)",U3*(V3/12),"")
Solved! Go to Solution.
Ah, I see. It's because you're trying to escape a number value to a text value.
For a calculated column, this should work:
Unsecured W Average [column] =
IF(
DATA[Loan Type] = "Status",
DATA[Deposit Amount] * (DATA[Annual Interest] / 12)
)
Pete
Proud to be a Datanaut!
Hi @sbarker_11 ,
1st approach :
Try this measure :
Measure =
CALCULATE(
SUMX (DATA, DATA[Deposit Amount]) * (DATA[Annual Interest]) /12),
DATA[LOAN TYPE] = "STATUS"
)
Another approach:
If you
1. create a column in the table data named IsLoanType_Status where IF ( Data[Loan Type] = "Status", 1 ,0 )
Then,
2. Create a measure:
SUMX (DATA, DATA[Deposit Amount]) * (DATA[Annual Interest]) /12)
3. On the visual containing the measure above, add the IsLoanType_Status is 1.
Please share sample data removing any sensitive information in case this doesn't resolve the issue.
Hi, this makes sense but when I have tried approach 1, i get the following error, maybe it needs summing? :
"A single value for column 'Annual Interest' in table 'DATA' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Hi @sbarker_11 ,
I may be reading this incorrectly, but it looks like it should be:
Unsecured W Average =
IF(
DATA[Loan Type] = "Status",
SUM(DATA[Deposit Amount]) * (DATA[Annual Interest] / 12),
""
)
Pete
Proud to be a Datanaut!
Hi,
I receive the following error Expressions that yield variant data-type cannot be used to define calculated columns.
Try creating it as a measure, not a calculated column.
Pete
Proud to be a Datanaut!
sadly the error message is from trying it as a measure:
"Expressions that yield variant data-type cannot be used to define calculated columns."
Ah, I see. It's because you're trying to escape a number value to a text value.
For a calculated column, this should work:
Unsecured W Average [column] =
IF(
DATA[Loan Type] = "Status",
DATA[Deposit Amount] * (DATA[Annual Interest] / 12)
)
Pete
Proud to be a Datanaut!
I need a calculated column to mirror this excel formula, measures arent working:
=IF(Z2="Fixed Loan (Unsecured)",U2*(V2/12),"")
if [Loan Status] = "Fixed Loan (Unsecured)" then [Deposit Amt] * (Annual Interest / 12) if not then blank.
i already have a measure for annual interest/12 which is
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
64 | |
61 | |
23 | |
17 | |
12 |