Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I'm not sure how to approach this challenge.
I have a table with a set of rules:
As you can see a case has a specific Fee_type when it meets all the conditions.
F.Ex. A case is Easywhen the
ft_cause = A and
the case_type = Type_A and
the customer = CustomerA and
Activities <5 and
Nr calls <1.
I am looking for a Measure or calculated column in my cases table that gives me right price:
Any idea on how to approach this?
Thanks in advance
Solved! Go to Solution.
You may try Pivot Column to shape data in Query Editor and then refer to this post.
@setis , what is the logic for desired price2 =200 for easy. Do you need this calculation on the aggerated number or at line level
@amitchandak thanks for looking into this.
I'm so sorry, there was a mistake in the table. I have just corrected it in the orogonal post. The easy price is 200.
The calculation I need is on a line level on the cases table.
Thank in advance
@setis , can you provide sample data, sample rule data in table format
@amitchandak Sure!
Sample rule
ft_name | ft_cause | Case_type | Customer | ft_operatorname | ft_number | Fee_type | Amount |
Cause | ME | CustomerA | Difficult | 1000 | |||
Case Type | Type_A | CustomerA | Difficult | 1000 | |||
Customer | CustomerA | Difficult | 1000 | ||||
Activities | CustomerA | Less than | 5 | Easy | 200 | ||
Cause | ME | CustomerA | Easy | 200 | |||
Customer | CustomerA | Easy | 200 | ||||
Case Type | Type_A | CustomerA | Easy | 200 | |||
Nr of calls | CustomerA | Less than | 1 | Easy | 200 |
Sample data
CaseID | ft_cause | Case_type | Customer | Nr of calls | Nr Activities | Desired Result(Fee_type) | Desired Result2(price) |
1221 | ME | Type_A | CustomerA | 0 | 4 | Easy | 200 |
1222 | AA | Type_B | CustomerA | 5 | BLANK | BLANK | |
1223 | ME | Type_A | CustomerA | 0 | 6 | Difficult | 1000 |
The weekend sank this post to the bottom of the forum. I'm bumping this to gain some monday morning visibility.
I would really appreciate any help.
@v-chuncz-msft thanks for your answer. You are right. That's definitiely the way to go.
I am having issues doing the unpivot in power query. Could you please help me with the steps to unpivot this correctly?
I have upload a sample here: https://drive.google.com/file/d/16wKtLuvvUJ8gMMV5Hk0PYFiEcCR8oAYL/view?usp=sharing
What I'm trying to go is from here:
To something like this:
At the same time, do you have any idea on how to solve the cells Activities and Calls so I have something usable to calculate with?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |