Forum Discussion
Custom Column with UseRelationship and If statement
Been using PowerBI successfully for a while now but am finding that DAX is my nemesis. I need to create a custom column that calculates a value from columns in two different tables with an inactive relationship, but only if another column has a "0" in it. So it would look something like this:
If Table1.ColumnA = 0 then SUM(Table1.ColumnB * Table2.ColumnA) else null, USERELATIONSHIP (Table1.ColumnC, Table2.ColumnB)
I know that's not exactly right, but every version I try results in various errors about missing parens, missing Commas, or Expression names not being recognized. Help?
HI jenneferparr,
It seems like you put Dax function and if statement usage into M query formulas that cause the issue.
M query is good at data structure shaping and conversations, I'd like to suggest you do these row contents calculations in Dax formula. (Notice: M query is case-sensitive, 'if' statement keyword should use 'lower' characters without '()' and it also not existed sum functions)What's the difference between DAX and Power Query (or M)?
If you confused about coding formula, please share some dummy data with a similar data structure to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
5 Replies
The two tables have an inactive relationship on the "Species_GroupID" column, and they both have an active relationship with another table on that same column.
Does that help?
HI jenneferparr,
It seems like you put Dax function and if statement usage into M query formulas that cause the issue.
M query is good at data structure shaping and conversations, I'd like to suggest you do these row contents calculations in Dax formula. (Notice: M query is case-sensitive, 'if' statement keyword should use 'lower' characters without '()' and it also not existed sum functions)What's the difference between DAX and Power Query (or M)?
If you confused about coding formula, please share some dummy data with a similar data structure to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi,
Could you try this formula:
Column =
IF (
'Table A'[Column A] = 0,
'Table A'[Column B] * RELATED ( 'Table B'[Column B] ),
'Table A'[Column B]
* CALCULATE (
VALUES ( 'Table B'[Column C] ),
USERELATIONSHIP ( 'Table A'[Table_ID], 'Table B'[FK_ID2] )
)
)
Screenshot below details:
Table A are first 3 columns (Table_ID, Column A, Column B). Next two columns are using related function just to see what are related values from Table B (so that you can check). Last column is final formula from above.
Logic: if column A = 0 then column A * related column B else column A * related column C using inactive relationship.
Cheers,
Nemanja
