Link two data tables to get column value from two different length part numbers

[Anonymous]

Hi All

I have two tables as seen below, with one table call Catalog # and the second table call Catalog Table. For the Catalog # table I need the Category Name pulled from the second table. Any help is welcome. Thanks

[Anonymous]

Thanks this works. I appreciate your help

[v-henryk-mstf]

Hi @Anonymous ,

According to the information you provide, you can create a one-to-one relationship between the two tables, and then add the catalong field to the slicer. I did a test for reference as follows:

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Thanks for the reply . So the first table Catelog # could have a short or long number and yes I can do the 1 to 1 relationship.

If the Catalog # is a long  or short number (can't control)  but I need to look up to the Catelog table and have power bi determine if the catalog # is short and long and pull the category name. Let me know if this provides clarity.

[selimovd]

Hey @Anonymous ,

would be easier when you show the details in Power BI. Now I can just guess they are connected in a 1:1 relationship.

In this case you can just use the RELATED function as calculated column:

Category Name = RELATED( 'Catalog Table'[Category Name] )

Otherwise you could also use LOOKUPVALUE to find the value.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi Denis

I have the relationship 1:1 setup from Catalog # to the to the catalog table to the short item number but struggle with the measure to pull the informaton of the Catelong table fields when the Catalong # is either a short or long number.

Thanks for your support

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Hi Ashish

Let me know if this helps. I dont have the ability to attach an excel file.

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi Ashish 

Thanks for the help and this does work. Just one more question. Is there is a way i can do this also without going into the query but doing this as a measure in a report?

[Ashish_Mathur · Accepted solution]

Hi,

Here's a solution using measures only.  Download the PBI file from here.