cancel
Showing results for
Did you mean:
Highlighted
Member

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

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

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!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

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

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?

2 REPLIES 2
Senior Member

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

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?

Member

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

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