Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

New Measure, search and return

Hello everyone,

 

Can you please help to tell:

How to create measure to: 

Sum Table2[Clicks] for certain Unique [Table1]Campaign Page ID are cointained in Table2[Page] under certain [Table1]Action Week?

e.g. Sum Clicks for all Unique [Table1]Campaign Page ID in "Week10", or Sum Clicks for all Unique IDs in "Update Copy"

 

Table2:

 

 

 

Thanks.

H

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI Anonymous,

    I'd like to suggest you add a new calculated column to table2 to extract the fact page id:

    PageID =
PATHITEM (
    SUBSTITUTE ( [Page], "/", "|" ),
    PATHLENGTH ( SUBSTITUTE ( [Page], "/", "|" ) )
)

    Then you can use this field and the date range of the 'week number' to summary records.

    Measure =
VAR currDate =
    MAX ( Table[Action Date] )
VAR _list =
    CALCULATETABLE (
        VALUES ( Table[Campaign Page ID] ),
        FILTER ( ALLSELECTED ( Table ), YEAR ( [Action Date] ) = currDate ),
        VALUES ( Table[Action Week] )
    )
RETURN
    CALCULATE (
        SUM ( Table2[Clicks] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [PageID]
                IN _list
                    && WEEKNUM ( [Date] ) = WEEKNUM ( currDate )
                    && YEAR ( [Date] ) = YEAR ( currDate )
        )
    )

    Regards,

    Xiaoxin Sheng

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous I'm not entirely certain what you are going for exactly but seems like a SUMX over a SUMMARIZE table. Question I have is what constitutes "unique ids"? Is that your Page?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Greg_Deckler ,

       

      Please let me clarify in a more clearer way.

      In Table1, [ID] stores value, they are repeated some times, by [Week] or [Type]

       

       

      the [ID] values contained in [Page] in Table2

       

      Please let me know if there is a way to create Measure, to Sum Impressions for all ID value by Week, or only for one week, such as: WK10.

      Repeated ID shall only be Sum by 1 time.

      That's say, in WK10, Sum of Impressions of news012808 shall not double.

       

      Appreciated if you can help.

       

      BTW: in fact I asked similar question in this post, but didn't have answer, so I tried to think and ask in another way like this post.

       

      Thanks.

      H

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        In an MS Excel file, paste both datasets and show the expected result very clearly, along with formulas