Diffrenet tables one Date filter

[gulevi]

Hello ,

1. I have a dashboard with two tables ( diffrenet ones with a date filed , EACH DATE FILED NAME IS DIFFRENET)

I want to create a filter (Slicer )  that will apply on both tables ,

for now all I have is 2 slicer one for each table.

Can anyone help ? (Important to : the filter needs to stay same as it is now , meaning a line you can chose on, as a normal date slicer)

2. I want the date Filter to be not just day and month but also TIme (in hours and min) , How can I do that .
For now All I have done was to chose slicer and the date field - which has a time but in the slicer I cant see it

[amitchandak]

I would suggest two table one for date and one for time . For time refer :https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

Have date column without the time and join the dates with the date calendar table

Date = date[Datetime].date

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[amitchandak]

@gulevi , Does the above replies helps. if you need more help make me @

Appreciate your Kudos.

[gulevi]

Hello @amitchandak 

unfortuantly it didnt work , I think the problem is that my Date table i created using CALENDERAUTO()

is without time , while my other 2 tables are with time.

Any chance that thisis the problem , and would anyway like to know how to create a slicerwith time as well.

Thanks.

[amitchandak]

@gulevi 

You should create a date column from datetime like give below

date = [Datetime].date

Or

Date = date(year( [Datetime]),month( [Datetime]),day( [Datetime]))

And join that with the calendar.

If required you can create hr dimension or time dimension

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

[gulevi]

Hey @amitchandak 

I dont understand what do you mean  ,  can youplease alobrate as in what should I do . 

for now what I did was creating the following table

{TimeTable =

VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Second", [Value])

RETURN

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable, SecondsTable),

"Time", TIME([Hour], [Minute], [Second])

) }

Now I have 2 tables , one calender and the other one timtable

 so I am not sure what do you mean hen saying join it with calander , how do I join them into one field ?

and make the relation to the other tables?

[amitchandak]

All you tables transaction(Fact) table will have date and time columns and they will join these date and time tables on date and time column. These Date calendar and time table will act as the master table will be used in the analysis.

If possible, share pbix file after removing sensitive information.

Like this

[gulevi · Accepted solution]

@amitchandak  

I dont have a date and time colum in my tables , and I prefer not to add columns to my tables(Since I already have a dateeith time column in them)

So instead I used the similare option you guys gave earlier in the thread

Just instead of using Calander() I created a table as follow(using kusto get data):

let _stepSize = 1m;

let _offset = 1d;

let _startTime = startofmonth(now(), -1) - _offset;

let _endTime = bin(now(), 1m) - _offset;

range Timestamp from _startTime to _endTime step _stepSize

| extend Timestamp = Timestamp + _offset

| extend WeekOffset = toint((startofweek(Timestamp) - startofweek(now())) / 7d)

| extend Timestamp = Timestamp - _offset

| order by Timestamp desc

and then I added the realations to them and it worked.

Thanks for helping ,

[v-chuncz-msft]

@gulevi 

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

[Anonymous]

Hi,

I suggest you create a date table or/and a time table and link the date fields from the 2 tables to the date/time table.

and use the date table as a filter for your 2 other tables

datetable: https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

timetable: https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

[Greg_Deckler]

Perhaps try creating a common table of just dates. Create a relationship between it and your 2 tables. You can use CALENDAR or CALENDARAUTO to create the date table.