Cross table if condition

[Anonymous]

Hi 

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 

Table1:                                                          Table2:

Subscriber            Individual Id                     Subscriber           Individual Id 

00012012             1254252                           00012012             1254252  

00012013              Null/Blank                       00012013             1254269 

00012014             1254255                           00012014             1254255

00012015             Null/Blank                        00012015             1254277

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) 

Sincerely - Krishna

[v-xicai]

Hi @Anonymous ,

You can create column like DAX below.

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])

Best Regards,

Amy

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.

[VasTg]

@Anonymous 

Since you have M:M relation you should use RELATEDTABLE expression to get the desired results.

Column = IF(ISBLANK('Table (1)'[Individual Id]),MAXX(RELATEDTABLE('Table (2)'),'Table (2)'[Individual Id]),'Table (1)'[Individual Id])

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.

If this helps, mark it as a solution.

Kudos are nice too.

[Anonymous]

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]

@Anonymous 

Did you create the column in POC_PYMT_ACTUAL table? Is the relationship between the tables based on Subscriber?

Also, are the column names correct? Pers Indv Id POC SBSPN LIFECYCLE ACTUAL

If this helps, mark it as a solution.If not please attach the sample pbix file.

[Anonymous]

Yeah, I have created this calculation in 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.

Thanks - Krishna.