Forum Discussion
Countif ?
Hi RvdHeijden,
Based on my understanding, you want to get the second example result form the first one, right? If it is, I try to reproduce your scenario and get expected result.
Create measures using the following formulas.count = CALCULATE(COUNTA(Test1[Name]),ALLEXCEPT(Test1,Test1[Name])) Total sick days = CALCULATE(SUM(Test1[Total SickDays]),ALLEXCEPT(Test1,Test1[Name]))
Create a table visual, you will get the expected result.
Please let me know if you have any questions.
Best Regards,
Angelia
With Query Editor I meant Edit Queries.
Apparently you are looking for a DAX solution.
i used your option and it works fine but now i want to expand on that idea.
I now have a colum which has the number of times that person called in sick but in the original table i also have a colum with the number of sickdays that person has and i want to combine that as wel
Name #Total SickDays
Piet 5
Piet 3
Piet 12
So now i have a new table with Names (unique) and the times he/she called in sick and the new colum should have the combined number of sick days
for Example
Name #Sick #Total SickDays
Piet 3 20
Your previous solution got me Column 1 and 2 ('Name' and '#Sick') but now i want column 3 as wel (#Total SickDays), how do i do that ?
#Total SickDays is a calculated column not a column in the original data
I would use the employee names as a lookup table then sum the number of sick days used from table 1 and sum the number of sick days available from table 2 then place those measures on the visual using the employee name for the axis or row from the look up table.
Then, I would create a measure for remaining sick days.
Sick days used = SUM (Table1[DaysSick])
Sick days available = SUM(Table2[SickDaysAvailable])
Sick Days Remaining = [Sick days available]-[Sick days used]
Or you could group the number of days on a different table and merge it together if you just wanted to keep on fact table.
Hi RvdHeijden,
Based on my understanding, you want to get the second example result form the first one, right? If it is, I try to reproduce your scenario and get expected result.
Create measures using the following formulas.count = CALCULATE(COUNTA(Test1[Name]),ALLEXCEPT(Test1,Test1[Name])) Total sick days = CALCULATE(SUM(Test1[Total SickDays]),ALLEXCEPT(Test1,Test1[Name]))
Create a table visual, you will get the expected result.
Please let me know if you have any questions.
Best Regards,
AngeliaThat worked like a charm :)
next question, i now have a table with the name, number of times someone called in sick and the total sickdays.
Some people arent employed with us anymore so i want to look if someone is stille employed but that data is in another table.
in excel it would be vert.zoeken or a lookup function but what is it in DAX ?
Hi RvdHeijden,
Glad to hear that your issue got solved. One thread is allowed to ask one qiestion, you'd reopen another case and post your sample data or .pbix file for analysis. Thanks for understanding.
Best Regards,
Angelia
