create a common date filter

[Elangamban]

Hi,

I need to create a common date filter for my tables. I've connect Dynamics crm to power bi.

Let's say I've three different team and for each of them has a table (sales, marketting, services). Those three tables has a date column.

How to create a common date filter,? so that if I chose date the dashboard should reflect the same. (The dashboard will contain data from all the three tables).

Thanks in advance for your reply!!

[tringuyenminh92]

Hi @Elangamban,

If you are using 

• Import Mode in connection, you could create Dates table in Modeling tab -> New Table -> Dates= CalendarAuto()
• DirectQuery Model: you need to handle the DimDate in CRM system side or switch to import mode

And you could support this idea by voting to make DimDates feature available for DirectQuery mode: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14809452-improve-direct-query-dat...

[Elangamban]

Hi,

Thanks for the reply.

Modeling tab -> New Table -> Dates= CalendarAuto() is not giving my solution. If  I select the date no changes is happening in the dashboard. Do I need to create relationship to Dates table from my tables?

[tringuyenminh92]

Hi @Elangamban,

Yes, you need choose Manage Relationship -> New to create relationship or auto dectect to have cross filter between dates table and your fact table 

[v-huizhn-msft]

Hi @Elangamban,

After you create relationship between table, you'd better set the slicer to contral several reports, or several slicer contal one report. You can click one silcer->Formua->Edit Interactions to create the connection with other slicers and any visualization in the window. For example, the relationship will be created if you select the funnel-shaped highlighted in red. Oppositely, there is no connection between slicer and visual if the highlighted circle in yellow is light. When you select the one slicer and connect it with other report, please see the following screenshot.



If you have any other question, please feel free to ask.

Best Regards,
Angelia

[Elangamban]

Hi Angelia,

Thanks for the reply, I'll try out that.

Anyhow, the following image is what I want to acheive.

Also I want to use the date in calendar table as Page level filter.

I hope this picture explains my requirements.

[v-huizhn-msft]

Hi @Elangamban,

Got it, the Sales, Services, Products and Total Report are in sample page or different pages?

Best Regards,
Angelia

[Elangamban]

Hi,

I've tried the date slicer filter, but its not displaying any data, no error, only the table headers.

Sales, Services, Products and Total Report will be in the same page.

Thanks.

[v-huizhn-msft]

Hi @Elangamban,

Based on my understanding, you want to display the fields highlighted in green line when you select "Bottom LIne", and the fields highlighted in red line when you select "Order Value", right? If it is, there must be relationship between Sales, Services, Products,Total Table and Date table. Filter is used to filter the rows, when you select one value in slicer, the corresponding rows in ralated table will be filtered. 

Best Regards,
Angelia

[Elangamban]

Hi,

For testing purpose, I've created relationship between Sales table and date table.

But, the data is not displaying when I chose the date table.

It will be helpful if you explain what kind of relationship should to be create? (For testing purpose lets assume these two tables, sales and date table)

Thanks...

[v-huizhn-msft]

Hi @Elangamban,

>>the data is not displaying when I chose the date table.

Do you import the data successfuly? What do you mean of "data is not displaying". It's weird.

Do you mind share the .pbix file for further analysis? 

Best regards,
Angelia