New Measure, search and return

[h_l]

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

[Greg_Deckler]

@h_l 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?

[h_l]

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]

Hi,

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

[h_l]

Hi @Ashish_Mathur ,

If in excel, I'll proceed by steps.

But the final data in step3 is need to be breakdown by date in chart, which is not applicable.

Here is the excel sample file.

And here is the PBI sample data.

The purpose is I can find a way, either by create Measure or any else, to get the Sum of Impressions and add into trend line.

Hope you can help.

Thanks in advance.

H

[v-shex-msft · Accepted solution]

HI @h_l,

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

[Ashish_Mathur]

Share the files over some other file hosting service such as Google Drive.  Mediafire is replete with ads.  When i click on Download, it just opens up an ad window.

[h_l]

Hi @Ashish_Mathur , Thanks for you remind! I haven't been awared of this issue yet!

Here is the downloadable sample PBI file shared from Microsoft OneDrive, hopefully it's downloadable and I may have your help.

[Ashish_Mathur]

Hi,

I have downloaded your PBI file but cannot understand what you want.