Custom Column with UseRelationship and If statement

[jenneferparr]

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?

[nandic]

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

[aj1973]

Hi @jenneferparr 

Can you share a sample of your model? 

Thanks

[jenneferparr]

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?

[Anonymous · Accepted solution]

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

[jenneferparr]

@Anonymous thank you for that, I clearly had the usage of DAX vs M turned around. I'll try a different tactic.

Just as an aside, it sure would be helpful for those of us who can't wrap our heads around programming languages if the team behind PowerBI would pick one language for everything. But I'll take that to the suggestions forum ...