Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Merging 2 tables several dates to one date

I have 2 tables I need to merge.

The first table looks like this

Page pathDate visitedEvent CategoryEvent ActionEvent LabelTotal Events
www.xxx.com9-1-22Outbound LinkclickABC2
www.xxx.com9-1-22Non Outbound LinkclickDEF1
www.hhh.com9-1-22Outbound LinkclickGHI3
www.xxx.com9-2-22Outbound LinkclickJKL1

 

It needs to be merged with a table like this:

Page PathDate VisitedPageviews
www.xxx.com9-1-2225
www.hhh.com9-1-2210
www.xxx.com9-2-225

 

My issue comes when trying to get the total pageviews number. After the merge, for page path www.xxx.com, the total pageviews for the date 9-1-22 would be 50 instead of 25 since there are 2 different kinds of event categories. I need the sum of pageviews to only sum one pageview number per date per page path, so the total should really be 25. 

 

I hope this made sense. Any help would be appreciated.

 

Thank you!

Need Help

3 Replies

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

    Anonymous after you merge, you can use following DAX measure to sum the page views

     

    Page Views Measure = 
SUMX ( 
   SUMMARIZE ( 
      Table, 
      Table[Page Path], 
      Table[Date Visited], 
      "@MaxPageViews", MAX( Table[Page Views] 
   ), 
   [@MaxPageViews] 
)

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    parry2k 

    This almost worked! I guess I didn't specify that each event category/action/label is associated with a specific client name which is within the event label. So if I filter the table by client, it doesnt count the pageviews for that day if the client didnt have any associated events. I need the pageviews to always be the same for an entire date range even if there were no events. Any ideas?

    Thank you so much!

    • v-luwang-msft's avatar
      v-luwang-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi  Anonymous ,

      Not quite sure what you are looking for, can you provide a sample to clarify what you are looking for?

       

       

      Best Regards

      Lucien