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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ReadTheIron
Helper III
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

DateProblem Code
1/2/2022ABC
1/3/2022CDE
1/3/2022ABC
1/4/2022XYZ

 

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

ProblemKey

Problem CodeProblem Description
ABCFlood
CDEFire
EFGHurricane
XYZSharknado

 

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

DateProblem CodeProblemFull
1/2/2022ABCFlood
1/3/2022CDEFire
1/3/2022ABCFlood
1/4/2022XYZSharknado

 

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
v-yangliu-msft
Community Support
Community Support

Hi  @ReadTheIron ,

 

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:

vyangliumsft_0-1673253608406.png

 

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @ReadTheIron ,

 

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:

vyangliumsft_0-1673253608406.png

 

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

Idrissshatila
Super User
Super User

Hello @ReadTheIron ,

 

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.

refer to this link https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9...

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

 

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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

@ReadTheIron,

 

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.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

 

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.