Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need help with the DAX form of Excel formulas below as I've tried and no luck and could use some help please:
F2FA -SUMIFS(B:B,A:A,A2,C:C,"Att",D:D,"<>on pp")
In SUMIFS (B:B,A:A,A2,C:C,"Att",D:D, in pp")
F2FP -SUMIFS(B:B,A:A,A2,C:C,"Pen",D:D,"<>on pp")
Please see below my sample and output data:
A B C D
Id | hours | state | guy | F2FA | in | F2FP |
A | 5 | that | gap | 40 | 0 | 0 |
A | 5 | that | face | 40 | 0 | 0 |
A | 5 | that | face | 40 | 0 | 0 |
A | 5 | that | face | 40 | 0 | 0 |
A | 5 | that | gap | 40 | 0 | 0 |
A | 5 | that | face | 40 | 0 | 0 |
A | 5 | that | gap | 40 | 0 | 0 |
A | 5 | that | gap | 40 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | face | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
B | 5 | that | gap | 120 | 0 | 0 |
C | 2.5 | feather | Rem | 25 | 0 | 5 |
C | 2.5 | feather | Rem | 25 | 0 | 5 |
C | 5 | that | one | 25 | 0 | 5 |
C | 5 | that | gap | 25 | 0 | 5 |
C | 5 | that | gap | 25 | 0 | 5 |
C | 5 | that | gap | 25 | 0 | 5 |
C | 5 | that | gap | 25 | 0 | 5 |
D | 3 | feather | Rem | 10 | 0 | 6 |
D | 3 | feather | Rem | 10 | 0 | 6 |
D | 5 | that | gap | 10 | 0 | 6 |
D | 5 | that | gap | 10 | 0 | 6 |
E | 2.5 | feather | Rem | 10 | 0 | 15.5 |
E | 2.5 | feather | Rem | 10 | 0 | 15.5 |
E | 2.5 | feather | Rem | 10 | 0 | 15.5 |
E | 3 | feather | Rem | 10 | 0 | 15.5 |
E | 2.5 | feather | Rem | 10 | 0 | 15.5 |
E | 2.5 | feather | Rem | 10 | 0 | 15.5 |
E | 5 | that | gap | 10 | 0 | 15.5 |
E | 5 | that | gap | 10 | 0 | 15.5 |
F | 3 | feather | Rem | 30 | 0 | 6 |
F | 3 | feather | Rem | 30 | 0 | 6 |
F | 5 | that | gap | 30 | 0 | 6 |
F | 5 | that | gap | 30 | 0 | 6 |
F | 5 | that | gap | 30 | 0 | 6 |
F | 5 | that | gap | 30 | 0 | 6 |
F | 5 | that | gap | 30 | 0 | 6 |
F | 5 | that | gap | 30 | 0 | 6 |
G | 2.5 | feather | Rem | 25 | 0 | 5 |
G | 2.5 | feather | Rem | 25 | 0 | 5 |
G | 5 | that | one | 25 | 0 | 5 |
G | 5 | that | gap | 25 | 0 | 5 |
G | 5 | that | gap | 25 | 0 | 5 |
G | 5 | that | gap | 25 | 0 | 5 |
G | 5 | that | gap | 25 | 0 | 5 |
H | 2 | feather | Rem | 0 | 0 | 2 |
I | 3 | feather | Rem | 80 | 0 | 6 |
I | 3 | feather | Rem | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
I | 5 | that | gap | 80 | 0 | 6 |
J | 2.5 | feather | Rem | 0 | 0 | 10 |
J | 2.5 | feather | Rem | 0 | 0 | 10 |
J | 2.5 | feather | Rem | 0 | 0 | 10 |
J | 2.5 | feather | Rem | 0 | 0 | 10 |
K | 2.5 | feather | Rem | 0 | 0 | 10 |
K | 2.5 | feather | Rem | 0 | 0 | 10 |
K | 2.5 | feather | Rem | 0 | 0 | 10 |
K | 2.5 | feather | Rem | 0 | 0 | 10 |
L | 3 | feather | Rem | 0 | 0 | 6 |
L | 3 | feather | Rem | 0 | 0 | 6 |
M | 5 | feather | gap | 0 | 0 | 10 |
M | 5 | feather | gap | 0 | 0 | 10 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
N | 5 | that | gap | 40 | 0 | 0 |
O | 5 | that | gap | 25 | 0 | 0 |
O | 5 | that | gap | 25 | 0 | 0 |
O | 5 | that | gap | 25 | 0 | 0 |
O | 5 | that | gap | 25 | 0 | 0 |
O | 5 | that | gap | 25 | 0 | 0 |
P | 5 | feather | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
P | 5 | that | gap | 35 | 0 | 5 |
Q | 5 | feather | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Q | 5 | that | gap | 40 | 0 | 5 |
Thank you
Solved! Go to Solution.
OK @moeconsult using the pattern supplied previously, those would look like the following. You would put ID in a visual and then you could have these measures:
Sum att no pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] <> "pp"
),
[Hours]
)
Sum att pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] = "pp"
),
[Hours]
)
Sum pen no pp =
SUMX(
FILTER(
'Table',
[Status] = "Pen" &&
[Type] <> "pp"
),
[Hours]
)
Hi,
Has your question been solved?
Yes thanks, I figured it out , sorry for the late reply
Not overly up on my Excel macro language syntax but in general you use something like this as a replacement for SUMIF(S):
SUMX(FILTER('Table',[Column] = "Something" && [Column1] = "Something else"),[Some Other Column])
You can also use || or OR instead of && for AND.
Tough to say exactly I see the sample source data but I don't see expected result or logic (not in Excel macro language) of how to get from source data to result. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Basically, what I am trying to archieve is :
Sum of hours where status = "att" and type <> "on pp" by id
Sum of hours where status = "att" and type = "on pp" by id
Sum of hours where status = "pen" and type <> "on pp" by id
Output column is color coded
ID | Hours | Status | Type | F2FA | On | F2FP |
A | 5 | Att | GaP | 40 | 0 | 0 |
A | 5 | Att | FACE | 40 | 0 | 0 |
A | 5 | Att | FACE | 40 | 0 | 0 |
A | 5 | Att | FACE | 40 | 0 | 0 |
A | 5 | Att | GaP | 40 | 0 | 0 |
A | 5 | Att | FACE | 40 | 0 | 0 |
A | 5 | Att | GaP | 40 | 0 | 0 |
A | 5 | Att | GaP | 40 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | FACE | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
B | 5 | Att | GaP | 120 | 0 | 0 |
C | 2.5 | Pen | Rem | 25 | 0 | 5 |
C | 2.5 | Pen | Rem | 25 | 0 | 5 |
C | 5 | Att | One | 25 | 0 | 5 |
C | 5 | Att | GaP | 25 | 0 | 5 |
C | 5 | Att | GaP | 25 | 0 | 5 |
C | 5 | Att | GaP | 25 | 0 | 5 |
C | 5 | Att | GaP | 25 | 0 | 5 |
D | 3 | Pen | Rem | 10 | 0 | 6 |
D | 3 | Pen | Rem | 10 | 0 | 6 |
D | 5 | Att | GaP | 10 | 0 | 6 |
D | 5 | Att | GaP | 10 | 0 | 6 |
E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |
E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |
E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |
E | 3 | Pen | Rem | 10 | 0 | 15.5 |
E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |
E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |
E | 5 | Att | GaP | 10 | 0 | 15.5 |
E | 5 | Att | GaP | 10 | 0 | 15.5 |
F | 3 | Pen | Rem | 30 | 0 | 6 |
F | 3 | Pen | Rem | 30 | 0 | 6 |
F | 5 | Att | GaP | 30 | 0 | 6 |
F | 5 | Att | GaP | 30 | 0 | 6 |
F | 5 | Att | GaP | 30 | 0 | 6 |
F | 5 | Att | GaP | 30 | 0 | 6 |
F | 5 | Att | GaP | 30 | 0 | 6 |
F | 5 | Att | GaP | 30 | 0 | 6 |
G | 2.5 | Pen | Rem | 25 | 0 | 5 |
G | 2.5 | Pen | Rem | 25 | 0 | 5 |
G | 5 | Att | One | 25 | 0 | 5 |
G | 5 | Att | GaP | 25 | 0 | 5 |
G | 5 | Att | GaP | 25 | 0 | 5 |
G | 5 | Att | GaP | 25 | 0 | 5 |
G | 5 | Att | GaP | 25 | 0 | 5 |
H | 2 | Pen | Rem | 0 | 0 | 2 |
I | 3 | Pen | Rem | 80 | 0 | 6 |
I | 3 | Pen | Rem | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
I | 5 | Att | GaP | 80 | 0 | 6 |
J | 2.5 | Pen | Rem | 0 | 0 | 10 |
J | 2.5 | Pen | Rem | 0 | 0 | 10 |
J | 2.5 | Pen | Rem | 0 | 0 | 10 |
J | 2.5 | Pen | Rem | 0 | 0 | 10 |
K | 2.5 | Pen | Rem | 0 | 0 | 10 |
K | 2.5 | Pen | Rem | 0 | 0 | 10 |
K | 2.5 | Pen | Rem | 0 | 0 | 10 |
K | 2.5 | Pen | Rem | 0 | 0 | 10 |
L | 3 | Pen | Rem | 0 | 0 | 6 |
L | 3 | Pen | Rem | 0 | 0 | 6 |
M | 5 | Pen | GaP | 0 | 0 | 10 |
M | 5 | Pen | GaP | 0 | 0 | 10 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
N | 5 | Att | GaP | 40 | 0 | 0 |
O | 5 | Att | GaP | 25 | 0 | 0 |
O | 5 | Att | GaP | 25 | 0 | 0 |
O | 5 | Att | GaP | 25 | 0 | 0 |
O | 5 | Att | GaP | 25 | 0 | 0 |
O | 5 | Att | GaP | 25 | 0 | 0 |
P | 5 | Pen | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
P | 5 | Att | GaP | 35 | 0 | 5 |
Q | 5 | Pen | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
Q | 5 | Att | GaP | 40 | 0 | 5 |
I hope this helps so you can help me
Thanks.
OK @moeconsult using the pattern supplied previously, those would look like the following. You would put ID in a visual and then you could have these measures:
Sum att no pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] <> "pp"
),
[Hours]
)
Sum att pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] = "pp"
),
[Hours]
)
Sum pen no pp =
SUMX(
FILTER(
'Table',
[Status] = "Pen" &&
[Type] <> "pp"
),
[Hours]
)
@Greg_Deckler Thanks for your help, I have third condition for the Measure but I was able to get it done with Dax below:
Sum att no pp =
Calculate(
Sum('Table',[Hours]),
'Table',
[Status] = "Att",
'Table',
[Type] <> "pp",
'Table',
[Achieve]="Acce")
Please let me know if you have any suggestion with the way have implemented this.
Regards
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |