cancel
Showing results for
Did you mean:
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
...

 Mass Cost 0.5 639 10 639 20 730 30 913 50 1187 100 1370 200 1643

 kurierstatetime zakaz client_id mass delivery/pick up out come 02.03.2021 20506 97297 0.22 pick up 02.03.2021 20506 97297 0.22 delivery 639 02.03.2021 20578 97931 8.05 pick up 02.03.2021 20577 97656 14.69 pick up 02.03.2021 20578 97931 8.05 delivery 639 02.03.2021 20577 97656 14.70 delivery 730 02.03.2021 20607 98026 8.00 pick up 02.03.2021 20601 98015 21.62 pick up 02.03.2021 20601 98015 21.62 delivery 913 02.03.2021 20607 98026 8.00 delivery 02.03.2021 20671 98196 45.40 pick up 913

1 ACCEPTED SOLUTION
Community Champion

The below are for creating calculated columns.

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

Max = MInMaxCreate[Mass]

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.

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

4 REPLIES 4
Community Champion

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

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.

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

Helper I

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

Community Support

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

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

Community Champion

The below are for creating calculated columns.

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

Max = MInMaxCreate[Mass]

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.

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

Announcements

#### 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.