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 All,
I'm a complete noob with DAX (come from SQL background) so apologies if my query is a bit basic.
I have a bunch of rows to store quantities by date. I also have a header row for these rows to inherit the unit price from
I'm trying to create a calculated column which multiplies QTY * (unit_price where ID = parent_id). If parent ID is blank the calculated column should be blank
Solved! Go to Solution.
Hi @Anonymous ,
Try this:
Column =
VAR UnitPrice_ =
LOOKUPVALUE ( 'Table'[Unit Price], 'Table'[Id], 'Table'[Parent Id] )
RETURN
[Qty] * UnitPrice_
Measure =
VAR UnitPrice_ =
LOOKUPVALUE (
'Table'[Unit Price],
'Table'[Id], SELECTEDVALUE ( 'Table'[Parent Id] )
)
RETURN
SUM ( 'Table'[Qty] ) * UnitPrice_
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try this:
Column =
VAR UnitPrice_ =
LOOKUPVALUE ( 'Table'[Unit Price], 'Table'[Id], 'Table'[Parent Id] )
RETURN
[Qty] * UnitPrice_
Measure =
VAR UnitPrice_ =
LOOKUPVALUE (
'Table'[Unit Price],
'Table'[Id], SELECTEDVALUE ( 'Table'[Parent Id] )
)
RETURN
SUM ( 'Table'[Qty] ) * UnitPrice_
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anybody have some other suggestions? The suggested answer is not working
Or should I be looking for a different solution?
@Anonymous , Create a new column like
new column =
var _1 = [parent_id]
return
maxx(filter(Table, [Id] =_1),[Unit Price]) * [qty]
Hi,
Thank you very much for the response @amitchandak .
That query fails for me, I can't create the VAR as parent_id as it's not a unique number. If I add a bit more of my dummy data you see I have multiple parent rows spread all throughout the data set
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |