Summing up, grouping by two parameters and transfer in new table

[Anonymous]

Hello peeps!

I, once again, have encountered a problem which I am not able to solve on my own.

Which tables do I have?

an "absence_list"

List of hours employees are supposed to do

DimDatesTable

What am I trying to do?

I am trying to create a new table and dynamically join a few collumns in.

Very important here, is to sum up the number of "isWorkday"s from the absence list BY user_id AND BY ListMonth.

Then I want to import other columns from other tables because of further calculations.

To give some context:

I am trying to find out how much employees have been there. The "net-working-time".

Therefore I'd had to import the sum of (absent) workdays (by user and ListMonth), normal set of hours employees are supposed to make etc. etc.

I guess this must be possible somehow. In SQL this simply would be a join.

I thought it might have to look something like this:

CALCULATE(SUM('absence_list'[isWorkday]);ALLEXCEPT('absence_list'[user_id];'absence_list'[ListMonth])

It doesn't work though. Also as a measure which doesn't really help me because I need the data in a table to do further calculations.

In case there is anyone with an idea in mind, please let me know!

Thank you and much love.

[v-lionel-msft]

Hi @Anonymous ,

you want to create a new table which inclueds a measure and some other columns:

Measure = 
CALCULATE(
    count([isWorkday]),
    ALLEXCEPT(
        table,
        [user_id],
        [List Month]
    )
)

But which are the other columns?

Maybe you can use SELECTCOLUMNS  ADDCOLUMNS  to create the new table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

thanks for the quick reply!

For clarification:

I want to condense the data into a new table and sum up the "isWorkday" by Month and user_id.

An example:

ID	user_id	ListMonth	SUM(isWorkday)
1	78	November 2019	6
2	56	December 2019	7
3	87	December 2019	12

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please try this DAX formula:

Table = 
SUMMARIZE(
    "absence_list",
    [id], [user_id],[listMonth],
    "sum(isWorkday)", SUM("absence_list"[isWorkday])
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

LIONEL CHEN. YOU ARE MY HERO. THAAAAAANK YOU SOOOOO MUCH!!!

All the best!!