topic Re: Sum and filter data from a separate table in DAX Commands and Tips

Sum and filter data from a separate table

Anonymous — Thu, 08 Jul 2021 08:58:37 GMT

Hi everyone!

I am a total beginner in DAX. I am using it inside a Power Pivot that I'm trying to summarize data with.

Here is the thing: I have two separate tables loaded in Power Pivot that contains historical employee data. In my first table I have the name, date, agent call data (length etc.). I want to combine that table with a separate table that contains name, date and telephony statuses so I can calculate and display the amount of time people spent in calls and various statuses.

In order to display all of that in a single pivot, I am losing my mind trying to combine all the data to a single table. I tried applying some Excel logic: filter the Table2 data by name, date and specific status, and then sum it all up in Table1, right?

CALCULATE(SUM(Table2[StatusDuration]),FILTER(Table2,Table2[Name]=Table1[Name]),FILTER(Table2,Table2[Date]=Table1[Date]))

The sum is not correct, as if I am missing something.

Thank you everyone for your help, let me know if you need additional data.

Re: Sum and filter data from a separate table

amitchandak — Thu, 08 Jul 2021 09:57:45 GMT

Anonymous , Is there a combination for which you can create 1-to many join between these two tables?

else you need common tables like name, date etc and analyze data together

Means a start schema - https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

Re: Sum and filter data from a separate table

Anonymous — Thu, 08 Jul 2021 10:01:37 GMT

Hi Amit!

This is an interesting read, I will check out what I can do about making a star schema. Currently there is no data I can combine in such a way, but it doesn't mean I can't make something, I have the benefit of editing the telephony report to a certain extent and I'll see what I can do there.