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

DAX Pricing based on multiple criteria

Dear PBI Community,

 

I would like to ask for some help with something that I cannot solve.

We have 3 tables:

1. FACT from Vendor using Vendor's ID and Vendor's QTY/UnitOfMeasure

2. Items with the conversion factor and our unique ID

3. Pricing for our unique ID based on multiple criteria (dates, qty breaks)

 

I am trying to "move" the correct Unit Price from Pricing table to FACT table (to calculate Sales).

 

Please find attached my pbix file.

(just 1 product for the sake of sharing)

PBI_UnitPrice 

 

I tried to put multiple FILTER criteria into my calculation but for some reason (I guess) the qty break part is wrong.

 

All help is much appreciated!

Thanks

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've looked at this... It's not possible to uniquely determine the pricing because you've got PriceList1 and PriceList2 and they are not disjoint for the same item. If an item falls into both lists, then there's no rule that says which one should be taken. For instance, if an item's price after conversion is between 10001 and 999999..., then you can have 2 different rows for the same item in the list that qualify based on dates and the price. This problem does not have a solution unless you'll define what should be done in the case of overlapping intervals.

Best
D

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Can't access the file.

Best
D
Anonymous
Not applicable

Hi @Anonymous ,

 

Just updated to link from OneDrive to Dropbox. Could you please try now?

 

Thanks

rszirmai

Anonymous
Not applicable

I've looked at this... It's not possible to uniquely determine the pricing because you've got PriceList1 and PriceList2 and they are not disjoint for the same item. If an item falls into both lists, then there's no rule that says which one should be taken. For instance, if an item's price after conversion is between 10001 and 999999..., then you can have 2 different rows for the same item in the list that qualify based on dates and the price. This problem does not have a solution unless you'll define what should be done in the case of overlapping intervals.

Best
D
Anonymous
Not applicable

Thank you so much for explaining the missing bit to me!

I have to think if I need an IF statement to prioritise one of the pricelist or just go with the MIN as it is within the current file.

Anonymous
Not applicable

Please share some sample data and expected output.

Thanks
Pravin
Anonymous
Not applicable

Hi Pravin,

 

Can you access the pbix file attached to my question?

 

Thanks

Anonymous
Not applicable

Sorry, I am not in office.
I am answering questions through phone.

Thanks
Pravin

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.

Top Solution Authors