Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a table in power BI, where I have to calculate via a measure the sum of the headcount for December only (the year does not matter), filtering 2 atributes also. I have tried the following measure, but it displayes blank as result:
Any suggestions on how to fix the measure?
Solved! Go to Solution.
@redface94
Pleaser try
TY Plan HC =
SUMX (
FILTER (
CALCULATETABLE ( gloria_view_all, ALL ( loria_view_all[Date] ) ),
gloria_view_all[Attribute] = "costs"
&& gloria_view_all[gloria_value_types] = "020"
&& MONTH ( gloria_view_all[Date] ) = 12
),
gloria_view_all[headcount]
)
CALCULATE(SUM(gloria_view_all[headcount]),
FILTER(gloria_view_all,
AND(gloria_view_all[Attribute]="costs",
gloria_view_all[gloria_value_types]="020",
MONTH(gloria_view_all[Date]=12)))))
It does not work, I get the following message: "Too many arguments were passed to the AND function. The maximum argument count for the function is 2."
thank you but unfortuntelly the proposed measured returns blank as result
@redface94
If the dax presented in your question returns a value then it would be extremely surprizing if my dax returns blank!
I took the following from your dax: gloria_view_all[Date] = 12 which I suspect perhaps not correct. Perhaps you mean to say: MONTH ( gloria_view_all[Date] ) = 12
sorry, I have tried so many formulas, I was confused. Yes, you are right, the result of my measure was blank. I tried with a different one and it was showing me only the value from that selected month, not December.
@redface94
Have you tried this?
TY Plan HC =
SUMX (
FILTER (
ALL ( gloria_view_all ),
gloria_view_all[Attribute] = "costs"
&& gloria_view_all[gloria_value_types] = "020"
&& MONTH ( gloria_view_all[Date] ) = 12
),
gloria_view_all[headcount]
)
Hi @redface94
Please try
TY Plan HC =
SUMX (
FILTER (
ALL ( gloria_view_all ),
gloria_view_all[Attribute] = "costs"
&& gloria_view_all[gloria_value_types] = "020"
&& gloria_view_all[Date] = 12
),
gloria_view_all[headcount]
)
@redface94
That was my last measure:
TY Plan HC =
SUMX (
FILTER (
ALL ( gloria_view_all ),
gloria_view_all[Attribute] = "costs"
&& gloria_view_all[gloria_value_types] = "020"
&& MONTH ( gloria_view_all[Date] ) = 12
),
gloria_view_all[headcount]
)
Changing the location of closing bracket to keep the 12 inside is wrong! please provide some screenshots for better understanding.
I think we are close in getting a good result! With the measure above now I can see the total but unfortunatelly it is not distinguishes between the HC type. The measure shows 962 everywhere, which is the total. Normally I have 919 Heads, thereof 1 leased, thereof 45 working students:
@redface94
Pleaser try
TY Plan HC =
SUMX (
FILTER (
CALCULATETABLE ( gloria_view_all, ALL ( loria_view_all[Date] ) ),
gloria_view_all[Attribute] = "costs"
&& gloria_view_all[gloria_value_types] = "020"
&& MONTH ( gloria_view_all[Date] ) = 12
),
gloria_view_all[headcount]
)
it works! thank you so much for all your support, much appreciated!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
23 | |
21 |