topic Re: Calculated column to retrieve value over one to many relationship including if ids not matched in DAX Commands and Tips

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

MJDS — Thu, 18 Jul 2024 10:26:24 GMT

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!

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

Anonymous — Fri, 19 Jul 2024 07:04:37 GMT

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

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

MJDS — Fri, 19 Jul 2024 09:44:13 GMT

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

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

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

SachinNandanwar — Fri, 19 Jul 2024 09:09:10 GMT

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

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

MJDS — Fri, 19 Jul 2024 09:16:08 GMT

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

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

SachinNandanwar — Fri, 19 Jul 2024 09:36:10 GMT

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.

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

MJDS — Fri, 19 Jul 2024 09:42:34 GMT

Here's my attempt at sample data:

Cases table:

CaseID

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

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

SachinNandanwar — Fri, 19 Jul 2024 09:46:53 GMT

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 ?

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

MJDS — Fri, 19 Jul 2024 09:50:48 GMT

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

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

SachinNandanwar — Fri, 19 Jul 2024 10:00:24 GMT

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