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]Actio...
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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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