Countif ?

[RvdHeijden]

I have a table with all the people that called in sick (a name is not unique in this list and can be in the list more then once) and that is just what i want to know.

I need a column that counts the number of times a name is in the list, preferably in the same table.

I want a Stacked Column Chart with on the X-Axis the number of times someone was sick

[MarcelBeug]

In the Query Editor you can group by name and add aggregation for the other data.

Next you can expand the nested table excluding the name.

Code example:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Naam", type text}, {"SomeText", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Naam"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"SomeText"}, {"SomeText"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"Naam", "SomeText", "Count"})
in
    #"Reordered Columns"

[RvdHeijden]

@MarcelBeug

Im sorry but i dont understand what you are saying, where can i find the Query Editor because when i go to 'Edit Queries' i only have an 'Advanced Editor' and not a 'Query Editor'.

Besides im looking for a formula but in your example is that something i can use in the visuals or just a a matrix

[MarcelBeug]

With Query Editor I meant Edit Queries.

Apparently you are looking for a DAX solution.

[RvdHeijden]

@MarcelBeug

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

[v-huizhn-msft · Accepted solution]

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

[RvdHeijden]

@v-huizhn-msft

That 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 ?

[v-huizhn-msft]

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

[RvdHeijden]

@v-huizhn-msft

Your right, ill close this one and start a new thread

[kcantor]

@RvdHeijden

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.