Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have two tables, one is the inventory table, the other is the transaction table. See below printscreen. What I am trying to achieve is the following: In the on hand table I want the comment to be displayed from the material transaction table (value column K displayed in column E). I want a lookup from the combination article number and subinventory from the on hand table, to the article and subinventory from the material transaction table. This my search criteria (in excel I build it in column D and H). To be displayed in the inventory table (column E) should be the comment of the maximum transaction date (column I) in relation to my search criteria. How should I achieve this by Power BI and dax? Step by step please. Many thanks in advance for your help!
Solved! Go to Solution.
Hi @Hyperchef1969 ,
Please try to add a calculated column in the Inventory table:
Comment =
VAR _a =
MAXX (
FILTER (
'Transaction',
[Search criteria] = EARLIER ( Inventory[Search criteria] )
),
[Transaction date]
)
RETURN
CALCULATE (
MAX ( 'Transaction'[Comment] ),
FILTER (
'Transaction',
[Search criteria] = EARLIER ( 'Inventory'[Search criteria] )
&& [Transaction date] = _a
)
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hyperchef1969 ,
Please try to add a calculated column in the Inventory table:
Comment =
VAR _a =
MAXX (
FILTER (
'Transaction',
[Search criteria] = EARLIER ( Inventory[Search criteria] )
),
[Transaction date]
)
RETURN
CALCULATE (
MAX ( 'Transaction'[Comment] ),
FILTER (
'Transaction',
[Search criteria] = EARLIER ( 'Inventory'[Search criteria] )
&& [Transaction date] = _a
)
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hyperchef1969 , try this:
- Create a calculate column in your transaction table:
Search_Criteria2 = IF (CALCULATE(max('Transaction table'[Transaction date]),
ALLEXCEPT('Transaction table','Transaction table'[Search_Criteria]))='Transaction table'[Transaction date],
'Transaction table'[Search_Criteria],"xxx")
- And in your Inventory table, create this calculate column:
Comment__ =
maxx(filter('Transaction table' ,
search('Transaction table'[Search_Criteria2],'Inventory Table'[Search Criteria],,0)>0),
'Transaction table'[Comment])
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |