Send alerts to users based on status changes

[Anonymous]

Hello Power Users,

I'm trying to send alerts when there is a data changes. For example if the status column changes from "Start"  to "In Progress" then i want to send an alert via email with the data. I know we have subscription alerts in Power BI Service but they are only for Whole numbers. In my case its a charaters. Below is my  data.

In the above example i would like to send an alert for the column name of "ABC"  and if status of that will change from Start to In Progress. i would like send only for that status not for all status.

The tricky part is the data in the datasource table will change every day(Basically the entire data in the table will delete at night and will get updated with new data). I would like to compare with yesterday data. i'm planning on creating a historic table in Power bi and keep adding on the new data but it should be in seperate table and would like to compare with new data. 

Any ideas/suggestions.

Thanks

[Anonymous]

Hi @Anonymous ,

Basically the entire data in the table will delete at night and will get updated with new data

If you want to compare data last day with data today, I think you need to keep last day data in your table. If you delete previous data at night, Power BI couldn't find your data. And we need a [Date] column to determind when is the data from.

Sample:

Measure:

ABC Change =
VAR _YEASTERDAY_STATUS =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER ( 'Table', 'Table'[Name] = "ABC" && 'Table'[Date] = TODAY () - 1 )
    )
VAR _Today_STATUS =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER ( 'Table', 'Table'[Name] = "ABC" && 'Table'[Date] = TODAY () )
    )
RETURN
    IF ( _Today_STATUS = "In progess" && _YEASTERDAY_STATUS = "Start", 1, 0 )

Then add alert in Power BI Service.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank for the response. It's not only for "ABC". It should be for Every name. When the status changes from Start to In Progress then have to send alerts.

[Anonymous]

Hi @Anonymous ,

I suggest you to try this measure.

Measure = 
VAR _SUMMAIZRE =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[ID],
        "Combine",
            VAR _YEASTERDAY_STATUS =
                CALCULATE (
                    MAX ( 'Table'[Status] ),
                    FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Date] = TODAY () - 1 )
                )
            VAR _Today_STATUS =
                CALCULATE (
                    MAX ( 'Table'[Status] ),
                    FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Date] = TODAY () )
                )
            VAR _COMBINE =
                COMBINEVALUES ( "-", _YEASTERDAY_STATUS, _Today_STATUS )
            RETURN
                IF ( _COMBINE = "Start-In Progress", 1, 0 )
    )
RETURN
SUMX(_SUMMAIZRE,[Combine])

Then manager your alert to send emails when value is greater than 0.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Check if DAX append method can help you to build sanpshot to compare

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/