cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

How to create a date slicer for 2 data fields

Hi,

 

In my PowerBI page, I have 2 widgets with 2 different tables. Both tables have a column 'date'.

I want to create 1 'data slicer' so that when I change the range of the date, both widgets will get update accordingly.

I create a Date Slicer, and add both 'Date' fields in each table to the 'Add data fields here' under Filter'.

sacheu_0-1610344245922.png

 

But when I test it, only 1 widget get update with the selected range of the date. Can you please tell me how to fix the issue?

 

Thank you.

 

 

1 ACCEPTED SOLUTION

Hi @sacheu,

Please take a look at the following link to know how to find out the root file link of files stored on a cloud server, then you can use power bi desktop to get data from the online file directly:

Use OneDrive for Business links in Power BI Desktop 
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
Community Support
Community Support

HI @sacheu,

You can try to extract the date values from two tables to create a calendar, that you can use this as the bridge to link to tables.
After these steps, you can use bridge calendar to create a slicer and it will apply filter effects to two table records.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks. How can I create a calendar from 2 tables?

Hi @sacheu,

You can try this calculate table formula if it works on your side:

New Table =
VAR _list =
    UNION ( ALL ( Table1[Date] ), ALL ( Table2[Date] ) )
RETURN
    CALENDAR ( MINX ( _list, [Date] ), MAXX ( _list, [Date] ) )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

I am using an Excel spreadsheet online as the data source of my PowerBI report. In this setting, I don't see a way which let me enter the above formula  in the report.

HI @sacheu,

These are dax expression which works on power bi desktop, if you directly upload your excel sheet to the power side as the source to create the report, you can't use these expressions. 

Power bi service does not support these edit features of calculated fields, please design your report on power bi desktop side and you will find the formula bar.

Comparing Power BI Desktop and the Power BI service 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks. But when I use 'Power BI desktop', it does not allow me to use and Online Excel spreadsheet as the data source of my report. The excel spreadsheet needs to be Online since it is periodically getting updates. I only let me import a local Excel spreadsheet.

HI @sacheu,

>> Translate the `Date’ column of the Table into Pacific Time zone

You can add a custom column on the query editor side to convert DateTime to UTC format and use 'DateTime.AddZone' function to calculate with the timezone offset, it will cover the date value to specific timezone values.
Notice: switch timezone functions not change the raw DateTime values.

>> Strip out the Time of the ‘Date’ column so that it just has the date (e.g. 1/19/2021 )

You can change the date field type to date, then it will not display the time parts of your values.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @sacheu,

Please take a look at the following link to know how to find out the root file link of files stored on a cloud server, then you can use power bi desktop to get data from the online file directly:

Use OneDrive for Business links in Power BI Desktop 
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors