Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am currently working with a dataset that has group pricing, meaning the customer's individual price is stored, but if they are part of a purchasing group, that price is also stored. I want the group price to take precedence over the individual price. I am struggling to have a way in DAX to create this right anti join to retrieve the group value if and only if the customer is in a group. Below is my current DAX logic and what it returns for visual reference. Thanks.
(Note: Here my desired return would only be the row with Group Flag of "1", not both the customer and group price, but still need to be able to see other parts and customers who may not have a group , so cannot simply filter if it has a groupflag or not)
Below is my attempt in DAX to pull all group values and then individual customer values, while having group take precedence over customer.
Solved! Go to Solution.
What if you filter your table to only return the row for each customer where the GROUPFLAG is minimal? I.e., the group price if it exists, but otherwise the customer price.
FILTER (
'Pricing Query',
'Pricing Query'[GROUPFLAG]
= CALCULATE (
MIN ( 'Pricing Query'[GROUPFLAG] ),
ALLEXCEPT ( 'Pricing Query', 'Pricing Query'[ABAN8] )
)
)
What if you filter your table to only return the row for each customer where the GROUPFLAG is minimal? I.e., the group price if it exists, but otherwise the customer price.
FILTER (
'Pricing Query',
'Pricing Query'[GROUPFLAG]
= CALCULATE (
MIN ( 'Pricing Query'[GROUPFLAG] ),
ALLEXCEPT ( 'Pricing Query', 'Pricing Query'[ABAN8] )
)
)
The above code worked for what I needed it to do, although I changed the MIN funciton to MAX. Thanks!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |