Forum Discussion

sharmisaranga09's avatar
sharmisaranga09
Icon for Helper I rankHelper I
2 years ago
Solved

How to get common timestamp from two tables timestamp column.

I have two table imported from Appinsights i.e, customEvents and pageViews, each has timestamp column in it. In powerbi I have table visual for each of these and trying to filter them by timetamp range from Filters Pane. I could select either of the timestamp from tables into filters pane so the other table isn't getting filtered as expected. Is there a way to get common timestamp by having all unique entries from both timestamp columns or how to approach this problem?

 

Thanks.

Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    sharmisaranga09 yes, you can create another table with unique values from both the tables set the relationship of this new table with both the tables, and use the column from this new table for filter, and that will filter both the tables.

     

    You can create this new dimension table in PQ (preferred), append two together, remove other columns, right-click on the time stamp, and select remove duplicates.

     

    With DAX, use following expression:

     

    New Time Stamp Table =
DISTINCT (
   UNION ( 
      ALLNONBLANKROW ( Table1[TimeStamp] ),
      ALLNONBLANKROW ( Table2[TimeStamp] )
   )
)

1 Reply

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    sharmisaranga09 yes, you can create another table with unique values from both the tables set the relationship of this new table with both the tables, and use the column from this new table for filter, and that will filter both the tables.

     

    You can create this new dimension table in PQ (preferred), append two together, remove other columns, right-click on the time stamp, and select remove duplicates.

     

    With DAX, use following expression:

     

    New Time Stamp Table =
DISTINCT (
   UNION ( 
      ALLNONBLANKROW ( Table1[TimeStamp] ),
      ALLNONBLANKROW ( Table2[TimeStamp] )
   )
)