Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Anti Right Join Dax Implementation

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.

Anti Join PBI 1.PNGAnti Join PBI 2.PNG

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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] )
        )
)

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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] )
        )
)
Anonymous
Not applicable

The above code worked for what I needed it to do, although I changed the MIN funciton to MAX. Thanks!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.