Forum Discussion

Alia2022's avatar
Alia2022
Regular Visitor
4 years ago
Solved

Date time filter issue

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

MikeJohnsonZA's avatar
MikeJohnsonZA
Responsive Resident
4 years ago

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's avatar
Alia2022
Regular Visitor
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.