Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
)
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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
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
)
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |