Forum Discussion
Sum and filter data from a separate table
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.
2 Replies
- amitchandak
Super User
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/
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.
