Sending E-mail report for only the top 10 entries from a report

[Fraz707]

Hi,

I've made a report which contains data of various users about and the Sql queries they executed, it has various columns like username, query, start time, end time, execution etc., now what I want to do is have a email sent out to me everyday based on the execution time which should contain all the columns for only the top 10 entries of each user rather than the whole report.

Is there any way to do this? An early reply would be appreciated as this is kind of urgent.

Thank you!

@Power bi

@developer 

[v-deddai1-msft]

Hi @Fraz707 ,

Would you please use the TOPN function to select the data you want :

Measure =

VAR a =

    TOPN ( 3, SUMMARIZE ( ALL ( 'Table' ), 'Table'[executiontime] ), 'Table'[executiontime], 0 )

RETURN

    IF (

        MAX ( 'Table'[executiontime] ) IN DISTINCT ( a ),

        MAX ( 'Table'[executiontime] ),

        BLANK ()

    )

Since report subscription does not retain filter in email image , so if you need a picture in the email to show the query for the top10  execution time , you should use the measure instead of filter directly on the visual.

Then try to subscribe yourself to the report: https://docs.microsoft.com/en-us/power-bi/service-report-subscribe.

Please also refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfV3b7LkpCpIjpTjma...

Best Regards,

Dedmon Dai

[Fraz707]

Hi @v-deddai1-msft  thanks for the reply, your solution really helped but I wanted that the report should be generated everyday based on the execution time for the current day only rather than considering the execution time for the whole time span, for this I used LASTDATE(table[TimeGenerated]) at the end of the measure instead of BLANK() but its giving error "Result: MdxScript(Model) (1, 46) Calculation error in measure 'ExampleTable'[Measure]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported"

Can you tell me what else I can use Instead of LASTDATE to get the desired output?

Thanks.

[v-deddai1-msft · Accepted solution]

Hi @Fraz707 ,

Would you please add the filter in summarize:

Measure =
VAR a =
    TOPN (
        3,
        SUMMARIZE (
            FILTER ( ALL ( 'Table' ), DAY ( 'Table'[TimeGenerated] ) = DAY ( TODAY () ) ),
            'Table'[executiontime]
        ),
        'Table'[executiontime], 0
    )
RETURN
    IF (
        MAX ( 'Table'[executiontime] ) IN DISTINCT ( a ),
        MAX ( 'Table'[executiontime] ),
        BLANK ()
    )

Best Regards,

Dedmon Dai