Link tables and need additional information to populate new created table

[Anonymous]

Hi Experts,

I have two tables below and have them link in a relationship by Catelog# and short item number. I can create the Data table below but was trying to find out how to get the addtional short item number listed and Category Name listed in the blank yellow spaces without using query, since i will not be able to meage the two tables together using query it will have to be a Dax measure.

Catalog #
2166243
2834884
2835380
283559600
2836639
283708200
283717700
283840900

Long Item Number	Short Item Number	Category Name
216624300	2166243	ES
283488400	2834884	MM
283717700	2837177	MM
283538000	2835380	FA
283663900	2836639	MM
283559600	2835596	MS
283708200	2837082	FA
283840900	2838409	SP

[v-xiaotang]

Hi @Anonymous 

if you want to use DAX expressions, try this

Category Name = 
var _length=LEN(MIN('Catalog'[Catalog #]))
return IF(_length=7,CALCULATE(MIN('Catalog Table'[Category Name]),FILTER('Catalog Table','Catalog Table'[Short Item Number]=MIN('Catalog'[Catalog #]))), CALCULATE(MIN('Catalog Table'[Category Name]),FILTER('Catalog Table','Catalog Table'[Long Item Number]=MIN('Catalog'[Catalog #]))))

Short Item Number = 
var _length=LEN(MIN('Catalog'[Catalog #]))
return IF(_length=7,CALCULATE(MIN('Catalog Table'[Short Item Number]),FILTER('Catalog Table','Catalog Table'[Short Item Number]=MIN('Catalog'[Catalog #]))), CALCULATE(MIN('Catalog Table'[Long Item Number]),FILTER('Catalog Table','Catalog Table'[Long Item Number]=MIN('Catalog'[Catalog #]))))

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous You could do it using LOOKUPVALUE or MAXX(FILTER(...),...). However, what I would suggest is to start with your Catalog Table query. Disable load on this query. Right-click and choose Reference. Remove the Long Item Number column and rename the Short Item Number column to just Item Number. Now, right-click the original query again. Remove the Short Item Number column. Rename Long Item Number to Item Number. Disable load on both of the queries. Create an Append query and append them together. At that point, your problem should be solved as you can relate your Catalog table to your Catalog Table table and everything will match.

[Anonymous]

Hi Greg

Thanks for the reply. I will try this but was trying to advoid using quieries as my dataset will allow this since its coming from corporate database. I will try the lookup value. Thanks

[Anonymous]

I think i fogure it out using the Lookup variable and using New Column . I use the measure 

Column = LOOKUPVALUE('Catalog Table'[Short Item Number],'Catalog Table'[Long Item Number],'Catalog'[Catalog #]) and got the following table results, in what I need so I can use the short item number and the Category name.

[v-xiaotang · Accepted solution]

thanks for your reply @Anonymous 

but sorry for not very understanding, do you mean you have solved this question? if yes, could you kindly accept the answer helpful as the solution. so the others can find it more quickly.
really appreciate!

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.