Calculated column to retrieve value over one to many relationship including if ids not matched

[MJDS]

I have a power BI desktop file with a direct query semantic model (which I can't amend). The cases table has a one to many relationship to the firms table, I need to use the CaseID column to retrieve the FIRM from the firms table to a calculated column in the cases table. I also need the formula to bring back "no firm" if there is no id match.

I've used Power BI for a while, but have always tried to use PowerQuery/M instead of DAX until now!

Here's the tables in question with fields highlighted:

Thanks!

M

[SachinNandanwar · Accepted solution]

Would assigning multiple firms for a given CaseId in comma seperated format suffice ?

Here is the sample data :

Case >>
CaseID,CaseName
1,Case A
2,Case B
3,Case C
4,Case D
5,Case E

Firm >>
FirmID,CaseId,Firm
1,1,Firm A
2,1,Firm B
3,1,Firm C
4,5,Firm X
5,4,Firm Y
6,5,Firm Z

This is the calculation for the column

FirmName = IF (ISBLANK(CONCATENATEX(RELATEDTABLE(Firm),Firm[Firm], ", ")),"No Firm", CONCATENATEX(RELATEDTABLE(Firm),Firm[Firm], ", "))

Regards,
Sachin Nandanwar

[MJDS]

Hi Sachin,

Thanks for replying - but in the Firms table, the Firm column will never be blank.  Only the cases table will have cases where there wouldn't be a firm associated.

M

[SachinNandanwar]

Hi,
Can you please use at the sample data I posted as reference and point me to where the firm column was blank.

So if I understand your requirement corerctly a single case can be assigned to multiple firms and there would be no firm that would have a case not assigned to it.

[MJDS]

Here's my attempt at sample data:

Cases table:

CaseID

1

2

3

4

Firms table:

CaseID	Firm
1	Firm A
3	Firm B
4	Firm C

I hope this explains it a bit better than I did first time!

Thanks

[SachinNandanwar]

So from your sample data would a unique caseid assigned to multiple Firms.

Lets take for example CaseId >>1.Now can it be assigned to say FirmA,FirmX and FirmY at the same time ?

[MJDS]

No, there would only ever be one firm associated with a CaseID.

[SachinNandanwar]

But in your initial requirement you mentioned there would be 1:M relationship across Case and Firms.

Anyways the solution I posted earlier would still work.

Case >>
CaseID,CaseName
1,Case A
2,Case B
3,Case C
4,Case D
5,Case E
6,Case F

Firm >>
FirmID,CaseId,Firm
1,1,Firm A
2,2,Firm B
3,3,Firm C
4,5,Firm X
5,4,Firm Y

CaseId >> 6 hasnt been assigned to any Firm,so it displays No Firm in the result below

[MJDS]

Thanks for replying! 🙂

I've put together some test data and I can give a bit more context and show you what the current result is and what the desired result would look like.

Cases table:

CaseID

1

2

3

4

Firms table:

CaseID	Firm
1	Firm A
3	Firm B
4	Firm C

The tables relate to a case/claims management system, and the cases table has one row for each case in the system (identified by caseID).  The firms table has a list of cases including the firms the cases/claims are against. They are still listed by one case per row.  
So if there isn't a firm recorded on the case in the case management system, it will only have a case ref row in the cases tables and not in the firms table.

Here's my current visual, I'm trying to show what share of claims made in  a year are against two particular firms.

However, this just shows the percentage of complaints against the firms for cases where a firm has been recorded.  Ideally what I'd like would be a fourth column for "no firm" for the cases where a firm hasn't been recorded.

Thanks!
M

[v-kongfanf-msft]

Hi @MJDS ,

Is it possible to provide some test data about the model and a screenshot of the desired results so that I can answer you as soon as possible?

Best Regards,
Adamk Kong