Date time filter issue

[Alia2022]

Hello Community,

This is my first question in this community. I hope i will get solution. Before i have to write my question, i want to say that this community is quite good and very supportive. I got a lot of solutions during my tasks. 

Question:

I have two data sets from two different sources. 

Data set 1 contains the following information:

DataTime (Format: DD.Month.Year hh:mm:ss)	ID number	Quantity	ID type	ID width	TT nr
22.12.2021 05:06:27	1235	1650	BB	22	abc
22.12.2021 05:14:47	1456	3000	BB	58	def

Data Set 2 have this information:

Date Time	Vi_cons.	WW_1	TT nr
22.12.2021 05:00:00	1588,83	4,00	abc
22.12.2021 05:05:00	1586,81	3,97	abc
22.12.2021 05:10:00	1586,36	3,79	def
22.12.2021 05:15:00	1584,69	4,08	def

I want to create a relationship bewteen these two data sets based on date time column and TT nr. column. But its not working. If i filter data set 1_ID number then no changing is happening at data set 2_Vi_cons & WW_1 columns. Sum of data set 2 is always giving the total sum. Its independant from ID nr. But Data set1_TT nr filter is working. 

Please help me to find the solution. May be am doing something wrong or is it necessary to be the same date time in both data sets?

Your support will really help me to understand the issue.

I hope i explain it correctly, if not then please let me know. 

Many Thanks

ahsan

[Alia2022]

Hi Rico Zhou,

Thanks for the hint. Now, its looking good. I tried it and it's working good. 

Again many thanks. 

BR

Ahsan

[MikeJohnsonZA]

Hi Ahsan

Yes, the dates would need to be exactly the same for the filtering to work. However this is not the best way to solve this problem. The best solution would be to create a Date table (see Create date tables in Power BI Desktop - Power BI | Microsoft Docs) then use this to filter both tables. to do this you would need the split your DateTime column into two separate columns, one for date and one for time. you then create a relationship from the date table to each of the new date columns in each of your tables.

~You can do the same thing for time if you need to analyse by time.

[Alia2022]

Hi Mike, 

Thanks for your answer. I tried it in the way as you explained but still not full working. Its better than before but not getting the correct data. 

BR

Ahsan

[Anonymous · Accepted solution]

Hi @Alia2022 ,

According to your statement, I know you create relationships between Date/time columns between two tables. Then Vi_cons and WW_1 will only show total sum. However Data set1_TT nr filter is working. 

As MikeJohnsonZA mentioned before, we need two date/time be the same then Power BI will filter correctly. Do you want to get sum of Vi_cons and WW_1 in "Data Set2" based on Date filter and _TT nr filter?

Add only Date columns in two tables, build relationships between only date columns and create the sum by measure.

Here I build an active relationship between [Only Date] columns and an inactive relationship between [TT nr].

Measure:

M_Vicons = CALCULATE(SUM('Data Set 2'[Vi_cons.]),USERELATIONSHIP('Data set 1'[TT nr],'Data Set 2'[TT nr]))

M_WW_1 = CALCULATE(SUM('Data Set 2'[WW_1]),USERELATIONSHIP('Data set 1'[TT nr],'Data Set 2'[TT nr]))

Result is as below.

You can download my sample to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.