Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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:
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
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:
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
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 👍
Proud to be a Super User! | |
EventTable doesn't appear in PowerQuery; it's a live connection stored with DirectQuery.
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 👍
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
88 | |
68 | |
67 |