- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
LOOKUP in SAME TABLE
Hi All,
I would like to ask assistance in the scenario below:
currently have a table with the first three columns, and I want to add a column for the Transaction Group.
Rules:
> If Item group is MB, get the first item group with same transaction no.
> If item is not MB, get row item group.
Transaction No | Item | Item Group | Transaction Group |
1 | 1001 | AN | AN |
1 | 1002 | AN | AN |
1 | 1015 | MB | AN |
2 | 1004 | RR | RR |
2 | 1015 | MB | RR |
3 | 1001 | AN | AN |
3 | 1002 | AN | AN |
3 | 1015 | MB | AN |
3 | 1020 | RR | RR |
Thank you.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Greg, this worked for my sample dataset. will check on the full dataset before accepting as solution. thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is this what you want?
You said you wanted the "row item group" if Item Group wasn't MB, but I wasn't sure what that meant. I pulled the item group from the same row.
Code here:
Column =
VAR varItemGroup = 'Table'[Item Group]
VAR varTransaction = 'Table'[Transaction No]
RETURN
IF(
varItemGroup = "MB",
MINX(
FILTER(
'Table',
'Table'[Transaction No] = varTransaction
),
'Table'[Transaction Group]
),
'Table'[Item Group]
)
Note: This is a calculated column formula and must be entered as a New Column to work.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi. noticed that you used the transaction group column in the formula. What i meant was that, I am trying to have the trans group column be the output.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ahh.. DIdn't understand TransactionGroup was the expected result. Try this @MSanchezz
Column =
VAR varItemGroup = 'Table'[Item Group]
VAR varItem = 'Table'[Item]
VAR varTransaction = 'Table'[Transaction No]
RETURN
IF(
varItemGroup = "MB",
MINX(
FILTER(
'Table',
'Table'[Transaction No] = varTransaction
&& 'Table'[Item] < varItem
),
'Table'[Item Group]
),
'Table'[Item Group]
)
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@MSanchezz I think I got it correct, please check, I did not use Transaction Group in my formula.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@MSanchezz Maybe:
Transaction Group =
IF(
"Item Group" = "MB",
VAR __First = MINX(FILTER('Table',[Transaction]=EARLIER([Transaction])),[Item])
RETURN
MAXX(FILTER('Table',[Transaction]=EARLIER([Transaction]) && [Item]=__First),[Item Group]),
[Item Group]
)
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Greg, this worked for my sample dataset. will check on the full dataset before accepting as solution. thank you.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
07-22-2024 08:36 PM | |||
05-14-2024 05:25 AM | |||
06-12-2024 09:02 AM | |||
Anonymous
| 10-05-2024 12:02 AM | ||
Anonymous
| 04-02-2024 06:27 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |