Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
@Anonymous
Since you have M:M relation you should use RELATEDTABLE expression to get the desired results.
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
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |