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.
Hi!
Right now my code looks like this, and works just fine:
#"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each if [PersonID] = [Previous.PersonID] and [Service] = [Previous.Service] and [Provider] = [Previous.Provider] and [Date] = [Previous.Date] +1 and [Day] = "Tuesday" and [Previous.Day] = "Monday" and [Previous.Date] <> [Stopdate] and ( ( [Pause] = 0 and [Previous.Pause] = 0 ) or ( [Pause] = 0 and [Previous.Pause] = 1 ) or ( [Pause] = 1 and [Previous.Pause] = 0 ) ) then 1 else 0,
I need to modify it according to a new variable I have received, preferably without having to create yet another column.
Basically, I need to enter the following after [Afregning] is determined to be 1 or 0:
If [Service] = [NameOfService] and [Frequency] = 1 then [Afregning] = [Afregning * 2]
[Frequency] is the new variable and only needs to be taken into account for one [Service]
I was thinking about just adding an "else if" after "then 1" to make a "then 2", but I'm not sure if I'd have to add all of the previous if/and to the else if?
The report is heavy as is, so I'd love to be able to find a solution that wouldn't have me needing to leave the computer on at night when I update the code 😉
Thanks in advance!
Solved! Go to Solution.
Try storing the code in a variable first
Something on these lines
#"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each let myvar=(if [PersonID] = [Previous.PersonID] and [Service] = [Previous.Service] and [Provider] = [Previous.Provider] and [Date] = [Previous.Date] +1 and [Day] = "Tuesday" and [Previous.Day] = "Monday" and [Previous.Date] <> [Stopdate] and ( ( [Pause] = 0 and [Previous.Pause] = 0 ) or ( [Pause] = 0 and [Previous.Pause] = 1 ) or ( [Pause] = 1 and [Previous.Pause] = 0 ) ) then 1 else 0) in (if [Service] = [NameOfService] and [Frequency] = 1 then myvar*2 else myvar),
Hi grggmrtn,
Sccording to your description, it seems that you calculate next expression based on first expression's value, right? If so, you could try to nest your result in expression like below to see whether it work or not
#"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each If [Service] = [NameOfService] and [Frequency] = 1 then 2*( if [PersonID] = [Previous.PersonID] and [Service] = [Previous.Service] and [Provider] = [Previous.Provider] and [Date] = [Previous.Date] +1 and [Day] = "Tuesday" and [Previous.Day] = "Monday" and [Previous.Date] <> [Stopdate] and ( ( [Pause] = 0 and [Previous.Pause] = 0 ) or ( [Pause] = 0 and [Previous.Pause] = 1 ) or ( [Pause] = 1 and [Previous.Pause] = 0 ) ) then 1 else 0) else 0
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @dax
Sorry, I may have explained myself wrong. There are many services, and for all but two of them the [Frequency] may have any value. But for two of them I need a different "Afregning" number if [Frequency] = 1, otherwise I need the original number.
If I'm reading your code correctly it starts by filtering out the service where I need the frequency, but doesn't bring the other services in as well?
Try storing the code in a variable first
Something on these lines
#"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each let myvar=(if [PersonID] = [Previous.PersonID] and [Service] = [Previous.Service] and [Provider] = [Previous.Provider] and [Date] = [Previous.Date] +1 and [Day] = "Tuesday" and [Previous.Day] = "Monday" and [Previous.Date] <> [Stopdate] and ( ( [Pause] = 0 and [Previous.Pause] = 0 ) or ( [Pause] = 0 and [Previous.Pause] = 1 ) or ( [Pause] = 1 and [Previous.Pause] = 0 ) ) then 1 else 0) in (if [Service] = [NameOfService] and [Frequency] = 1 then myvar*2 else myvar),
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |