cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Replace codes with full text based on table

I have one table of records that uses abbreviations to describe problem codes, like this:

EventTable

 Date Problem Code 1/2/2022 ABC 1/3/2022 CDE 1/3/2022 ABC 1/4/2022 XYZ

I have another table with a key to the problem codes:

ProblemKey

 Problem Code Problem Description ABC Flood CDE Fire EFG Hurricane XYZ Sharknado

I'd like to create a calculated column in EventTable that shows the full text of Problem Description

 Date Problem Code ProblemFull 1/2/2022 ABC Flood 1/3/2022 CDE Fire 1/3/2022 ABC Flood 1/4/2022 XYZ Sharknado

I know I can relate the tables on ProblemCode, but I want to use the full text of Problem Description in various graphics using data from EventTable. I'm fairly sure this is a simple operation, but I'm drawing a blank. Any help appreciated!

1 ACCEPTED SOLUTION
Community Support

Here are the steps you can follow：

1. Create measure.

``````Measure =
var _select=SELECTCOLUMNS('EventTable',"1",[Problem Code])
return
MAXX(
FILTER(ALL('ProblemKey'),
'ProblemKey'[Problem Code] in _select),[Problem Description])``````

2. Result:

Best Regards,

Liu Yang

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

5 REPLIES 5
Community Support

Here are the steps you can follow：

1. Create measure.

``````Measure =
var _select=SELECTCOLUMNS('EventTable',"1",[Problem Code])
return
MAXX(
FILTER(ALL('ProblemKey'),
'ProblemKey'[Problem Code] in _select),[Problem Description])``````

2. Result:

Best Regards,

Liu Yang

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

Super User

First of all, go to Power Query and merge both tables with the event table and get the description columns only from both tables.

Refer to this link for instructions https://learn.microsoft.com/en-us/power-query/merge-queries-overview

then you merge these columns to show in one column and you can use it.

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Helper III

EventTable doesn't appear in PowerQuery; it's a live connection stored with DirectQuery.

Super User

Then merge only the other two tables and then merge the problem description columns and you can use it since there's a relation between them and event table.

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Helper III

Sorry, I'm having a bit of trouble understanding. I only have two tables, EventTable (which is a live connection and which I can't access in PowerQuery) and ProblemKey, which is a simple Excel import and which I can access in PowerQuery. I can create calculated columns for EventTable, but I don't know how to access it to merge.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors