Count Distinct the number of values matching keyword based off another table

[trdoan]

Hi everyone,

I have this table called "Data":

Code	Description
100	PT
102	HT
105	FT

And this table called "OPR", linked to each other by Code and Serial columns:

Serial	Material Group	Store
105	A1	1
107	A2	2
114	A23	1
119	A32	15
257	A1	16
258	A1	2
102	A23	1
159	A45	2
102	A53	2

Can you please help me to:

1. Create 2 Calculated Columns in the 'Data' table that show the corresponding Store Name and Material Group (if any)

2. Create a measure to calculate the number of Material Groups for each Description group from the 'Data'[Description] column?

Ex: 

# of Material Groups for PT = 0 ( if any of the Codes cannot be found in the 'OPR' table, return 0 instead of (Blank) )

# of Material Groups for HT = 2

# of Material Groups for FT = 1

3. Create a measure to show what are the Material Groups for PT, HT, and FT that appear in the 'OPR' table

Ex:

List of Material Groups for PT = None

List of Material Groups for HT = A23, A53

List of Material Groups for FT = A1

Thank you very much!!!

[Anonymous · Accepted solution]

For #1, load both tables into Power Query

• Merge Data with OPR, left outer join
• Expand OPR to show Material Group and Store

I do not follow #2 though. After loading both of those tables into PBI, you will have a relationship between those two tables.  How are you getting # of Material Groups for HT = 2?  HT in the Data table has a corresponding Code of 102. When looking for 102 in the OPR table there is only one row, so not sure where that 2 is coming from?

[trdoan]

Hi @Anonymous thank you for the advice on Q1 and I apologised for the counting mistakes. Your solution worked! Thanks again!