Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

HELP Required :: Related Tables DAX Query :: New column based on Text

Hello Power BI champions

 

I have two Related tables Table A to Table B ( 1 to many )

 

 

Table A - With Product IDs ( Unique ) and one Entry per Product  ID 

 

Table B - With multiple entries of the same Product IDs with their related part Numbers and at part Level compliance YES or NON-C

Based on that data  

 

I want to have one column against each unique Product ID in Table- A if any of the active parts are NON-C for that Product ID in Table B, then the whole product will become Not  Compliance in Table A

 

 

Table A 

Product ID

19CB001

19IT500

18NP001

19IT001

19IT005

 

 

Table B 

 

Product ID

 Part ID 

Part State 

Part  Compliance

19CB001

1

Active

YES

19CB001

2

Active

YES

19CB001

3

Active

NON-C

19IT500

1

Active

YES

19IT500

2

Active

YES

19IT500

3

Closed

NON-C

19IT500

4

Active

YES

18NP001

1

Hold

YES

18NP001

2

Hold

NON-C

19IT001

1

Active

NON-C

19IT005

1

Active

YES

 

 

Table C:: Desired Output

 

Product ID

Desired Results

19CB001

Not Compliance

19IT500

Compliance

18NP001

Compliance

19IT001

Compliance

19IT005

Compliance

 

Thanks 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

[Product Compliance] = -- calculated column
var __prodID = TA[Product ID]
var __notCompliant =
	NOT ISEMPTY(
		FILTER(
			RELATEDTABLE( TB ),
			TB[Part State] = "active"
			&&
			TB[Part Compliance] = "non-c"
		)
	)
return
	if ( __notCompliant, "Not Compliant", "Compliant" )

Best

Darek

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

[Product Compliance] = -- calculated column
var __prodID = TA[Product ID]
var __notCompliant =
	NOT ISEMPTY(
		FILTER(
			RELATEDTABLE( TB ),
			TB[Part State] = "active"
			&&
			TB[Part Compliance] = "non-c"
		)
	)
return
	if ( __notCompliant, "Not Compliant", "Compliant" )

Best

Darek

Anonymous
Not applicable

its showing True or False , which i converted later , thanks for the help , appreciated 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.