Cross table if condition

Question

Hi&nbsp;I have two tables which are joined M:M. I am trying to create a column considering either of the table I was not able to see other table columns in the calculation. Please advise&nbsp;Table1:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table2:Subscriber&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Individual Id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Subscriber&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Individual Id&nbsp;00012012&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1254252&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;00012012&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1254252&nbsp;&nbsp;00012013&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Null/Blank&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;00012013&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1254269&nbsp;00012014&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1254255&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;00012014&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;125425500012015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Null/Blank&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00012015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1254277&nbsp;I am creating column in Table 1 but not able to locate Table2 fields=IF(ISBLANK(Table1. Individual ID), Table2.Individual ID, Table1. Individual ID)&nbsp;&nbsp;Sincerely - Krishna&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

vastg · Answer

Anonymous&nbsp;&nbsp;Since you have M:M relation you should use RELATEDTABLE expression to get the desired results.&nbsp;Column = IF(ISBLANK('Table (1)'[Individual Id]),MAXX(RELATEDTABLE('Table (2)'),'Table (2)'[Individual Id]),'Table (1)'[Individual Id])&nbsp;&nbsp;&nbsp;The "Individual ID" column should be in numeric datatype for this to work. Based on your need, change the aggregate expression, I have used MAXX as a example.&nbsp;If this helps, mark it as a solution.Kudos are nice too.&nbsp;

anonymous · Answer

thanks for the quick response, I try to implement the same but I am getting the below error

'A single value for column 'Pers Indv Id' in table 'POC_PYMT_ACTUAL' 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.'

= IF(ISBLANK('POC_PYMT_ACTUAL'[Pers Indv Id]),MAXX(RELATEDTABLE('POC_SBSPN_LIFECYC_ACTUAL'),'POC_SBSPN_LIFECYC_ACTUAL'[Pers Indv Id POC SBSPN LIFECYCLE ACTUAL]),'POC_PYMT_ACTUAL'[Pers Indv Id])

Sincerely- Krishna

vastg · Answer

Anonymous&nbsp;&nbsp;Did you create the column in&nbsp;POC_PYMT_ACTUAL table? Is the relationship between the tables based on Subscriber?&nbsp;Also, are the column names correct?&nbsp;Pers Indv Id POC SBSPN LIFECYCLE ACTUAL&nbsp;&nbsp;If this helps, mark it as a solution.If not please attach the sample pbix file.&nbsp;&nbsp;

v-xicai · Answer

Hi Anonymous&nbsp;,
&nbsp;
You can create column like DAX below.
&nbsp;
Column = Var d=CALCULATE(FIRSTNONBLANK(Table2[Individual Id],1),FILTER(ALLSELECTED(Table1),Table1[Subscriber]=Table2[Subscriber]))
Return
IF(ISBLANK(Table1[Individual Id]),d,Table1[Individual Id])
&nbsp;
Best Regards,
Amy
&nbsp;
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Yeah, I have created this calculation in&nbsp;POC_PYMT_ACTUAL table and yes the relationship established with Subscriber. I tried creating the calculation referring same but of getting Max per row, grouping is not getting applied. Please advise.&nbsp;Thanks - Krishna.

Forum Discussion

Cross table if condition

5 Replies

Related content

A cross table complex condition

Cross tab table

Cross datawarehouse queries

Conditional Formatting with Matrix Table

Cross table Datediff