cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trdoan Member
Member

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

Hi everyone,

 

I have this table called "Data":

 

CodeDescription
100PT
102HT
105FT

 

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

 

SerialMaterial GroupStore
105A11
107A22
114A231
119A3215
257A116
258A12
102A231
159A452
102A532

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M Senior Member
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
Nick_M Senior Member
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?

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