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

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, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

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, @Anonymous 

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, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.