multiple data sources, multiple times

[newbies13]

It seems like this shouldn't be this difficult, but perhaps I am missing something.  I am making an IT helpdesk dashboard, we have 3 different teams with different queues, IE. 3 different data sources. Each of these queues have similar data points, open dates, closed dates, etc. 

I am trying to combine all this data into an overview dashboard across the 3 teams to get an idea of all the normal metrics. The issue is when I try to filter the data to show just a month or just a year, I have to use a date filter from every data source to filter them all properly. The solution to this I would think is a date table with a relationship to each data source and to use that to filter all 3 at once. 

I've set all that up, but when I go to filter power bi returns 0 results regardless of what I choose. Show me all tickets for the year, nothing. Show me all tickets for the month, nothing. Now if I use the date filter on just 1 of the data sources that works fine. If I connect the date table to all 3 data sources it fails for all of them.

Any help would be appreciated.   

[David_334]

I would like to create a dashboard that will take the most recent value from multiple excel spreadsheets. In summary there will be 3-4 different microsoft forms used to audit/register transfers of items from the main storage area. Within the store there are 15 items, and I would like the most recent entry for each item (from any of the microsoft forms/excel spreadsheet to be the uploaded and shown in the dashboard.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-juanli-msft]

Hi @newbies13

As tested, i create a data table with the DAX formula below, then manage the relationships between "calendar" table and three data tables.

calendar = CALENDARAUTO()

Then i can select items in the slicer, the three tables would show the value according to the slicer.

Best Regards

Maggie

[newbies13]

Hi Maggie, 

Would the datasets having multiple dates affect this?  For example, if you were to add a "closed date" along with the open date in each of your data sets? 

Also, thank you for the CALENDERAUTO() code, I didn't know such a thing existed and that is helpful on it's own. 

[v-juanli-msft · Accepted solution]

Hi @newbies13

When add a "closed date" along with the open date in each of your datasets, it also runs.

Please note that only create relationship between two tables based on one column from each table.

For example, create relationships 

Table1                               relationship               Table2

"data1"[open date]          (many to one)           "calendar"[date]

"data2"[open date]          (many to one)           "calendar"[date]

"data3"[open date]          (many to one)           "calendar"[date]

Or

Table1                               relationship               Table2

"data1"[closed date]        (many to one)           "calendar"[date]

"data2"[closed date]        (many to one)           "calendar"[date]

"data3"[closed date]        (many to one)           "calendar"[date]

Best Regards

Maggie