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.
Hello, I am trying to create a column that expresses whether something is a discount or not based on criteria within a table.
Below is the data set:
BookingID | ExtraItemQty | ExtraItemExtendedCost |
25220696 | 12 | 900 |
25220883 | 7 | 525 |
25220692 | 4 | 300 |
25220876 | 24 | 1800 |
25220696 | 12 | -180 |
25220876 | 24 | -360 |
25220693 | 2 | 150 |
25220889 | 2 | 150 |
25220885 | 2 | 150 |
25220701 | 2 | 150 |
My desired output is as follows:
BookingID | ExtraItemQty | ExtraItemExtendedCost | Type |
25220696 | 12 | 900 | Discount |
25220883 | 7 | 525 | Full Price |
25220692 | 4 | 300 | Full Price |
25220876 | 24 | 1800 | Discount |
25220696 | 12 | -180 | Discount |
25220876 | 24 | -360 | Discount |
25220693 | 2 | 150 | Full Price |
25220889 | 2 | 150 | Full Price |
25220885 | 2 | 150 | Full Price |
25220701 | 2 | 150 | Full Price |
So any ID with a negative ExtraItemExtendedCost would be considered a discount, and if there is not negative value for the ID at some point it is considered Full Price.
Thank you
Solved! Go to Solution.
New column =
var _cnt = countx(filter(Table, Table[BookingID] = earlier([BookingID]) && [ExtraItemExtendedCost] <0), [BookingID] )
return
If(isblank(_cnt) , "Full Price", "Discount")
New column =
var _cnt = countx(filter(Table, Table[BookingID] = earlier([BookingID]) && [ExtraItemExtendedCost] <0), [BookingID] )
return
If(isblank(_cnt) , "Full Price", "Discount")
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |