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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
trdoan
Helper III
Helper III

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
Anonymous
Not applicable

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?

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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