Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
15 |
User | Count |
---|---|
38 | |
20 | |
19 | |
16 | |
10 |