cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Azat_Aliaskarov
Helper I
Helper I

Taking price from price list that is formed in range and by row

Hi
One more question

I have 2 tables, cost of delivery by mass, and delivery and delivery information table, see below
I need to create measure, not a column, to put the cost of the delivery with the followin logic:

The mass of 1st delivery is 0.22, so it falls between 0.5-10, so we take the max cost equal to 639 between these 2 masses
The mass of 2st delivery is 8.05, so it falls between 0.5-10, so we take the max cost equal to 639 between these 2 masses
The mass of 1st delivery is 14.70, so it falls between 10-20, so we take the max cost equal to 730 between these 2 masses
...

MassCost
0.5639
10639
20730
30913
501187
1001370
2001643

 

 

kurierstatetimezakazclient_idmassdelivery/pick upout come
02.03.202120506972970.22pick up 
02.03.202120506972970.22delivery639
02.03.202120578979318.05pick up 
02.03.2021205779765614.69pick up 
02.03.202120578979318.05delivery639
02.03.2021205779765614.70delivery730
02.03.202120607980268.00pick up 
02.03.2021206019801521.62pick up 
02.03.2021206019801521.62delivery913
02.03.202120607980268.00delivery 
02.03.2021206719819645.40pick up913

 

@Ashish_Mathur 

@Greg_Deckler 

@Jihwan_Kim 
@marcorusso 

@AlB 

1 ACCEPTED SOLUTION

Hi, @Azat_Aliaskarov 

Thank you for your feedback.

The below are for creating calculated columns.

 

Min =
COALESCE (
CALCULATE (
MAX ( MInMaxCreate[Mass] ),
FILTER ( MInMaxCreate, MInMaxCreate[Mass] < EARLIER ( MInMaxCreate[Mass] ) )
),
0
)

 

Max = MInMaxCreate[Mass]
 
The link down below is for your reference.
 
Please keep in mind that if more columns are involved, for instance, location, country, suppliers, ...., then those have to be changed.
 
In my opinion, creating a MinMaxTable is very easily done in excel.
If it is allowed, copy the original table to excel, create Min and Max column, and pull it into the power bi.
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

4 REPLIES 4
Jihwan_Kim
Community Champion
Community Champion

Hi, @Azat_Aliaskarov 

Please check the below.

I am not sure I understood your logic correctly.

What I understood is,

if delivery is done, then cost is mention in the delivery row

if the only pickup is done, and not yet delivered, then cost is mentioned in the pickup row

 

sample pbix file's link is down below.

 

Outcome =
VAR currentclientid =
MAX ( Deliveries[client_id] )
VAR conditions =
COUNTROWS (
FILTER ( ALL ( Deliveries ), Deliveries[client_id] = currentclientid )
)
RETURN
COALESCE (
IF (
conditions = 2,
SWITCH (
TRUE (),
SELECTEDVALUE ( Deliveries[delivery/pick up] ) = "delivery",
CALCULATE (
MAX ( Costs[Cost] ),
FILTER (
Costs,
SELECTEDVALUE ( Deliveries[mass] ) > Costs[Min]
&& SELECTEDVALUE ( Deliveries[mass] ) <= Costs[Max]
)
),
BLANK ()
),
IF (
conditions = 1,
CALCULATE (
MAX ( Costs[Cost] ),
FILTER (
Costs,
SELECTEDVALUE ( Deliveries[mass] ) > Costs[Min]
&& SELECTEDVALUE ( Deliveries[mass] ) <= Costs[Max]
)
)
)
),
""
)

 

 

https://www.dropbox.com/s/5gn5y9l8go16zjn/azat.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

I think you understood logic correctly based on file that you send and outcome column.
I just have one question how can I create max and min columns without manual data entry,
because this table is actually really long, it includes different tarif fror different cities, it also store historical tarifs
It is ok if you show how make max and min based on simplified version of this table

Azat_Aliaskarov_0-1619066412696.png

 

Hi, @Azat_Aliaskarov 

I have checked the attached pbix file ,@Jihwan_Kim ‘s solution  seems to be able to meet your needs.

Could you please tell me whether your problem has been solved?

If yes, you could accept the helpful answer as solution to close this thread. It will help other community members easily find the solution when they get the similar problem.

Best Regards,
Community Support Team _ Eason

 

Hi, @Azat_Aliaskarov 

Thank you for your feedback.

The below are for creating calculated columns.

 

Min =
COALESCE (
CALCULATE (
MAX ( MInMaxCreate[Mass] ),
FILTER ( MInMaxCreate, MInMaxCreate[Mass] < EARLIER ( MInMaxCreate[Mass] ) )
),
0
)

 

Max = MInMaxCreate[Mass]
 
The link down below is for your reference.
 
Please keep in mind that if more columns are involved, for instance, location, country, suppliers, ...., then those have to be changed.
 
In my opinion, creating a MinMaxTable is very easily done in excel.
If it is allowed, copy the original table to excel, create Min and Max column, and pull it into the power bi.
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors