Alerts - Value Based or Row Level Security

[TCavins]

I have a set of data that has a column for Y/N. If the count of Y's > 0, I'm going to set off an alert. However, I want the alerts to go to specific people depending on the value in another column. Almost like Row Level Security. In the image below, I would count the number of Y's in column B. If greater than 0, I'd send an alert to people but it would be different for each value in column A. Each Category would need a different person to be alerted and only see that information along with other data in the alert. Is this possible?

BobSmith@someemail.com would get alerts for A

ShannonJohnson@someotheremail.com would get alerts for Category B and so on.

[v-alq-msft · Accepted solution]

Hi, @TCavins 

I'd like to suggest you use Data alerts in the Power BI service . I created data to reproduce your scenario.

Table:

You may create a measure for each category as below.

CountAlertA = 
var _retult = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Category] = "A"&&
            'Table'[AlertTrigger] = "Y"
        )
    )
)
return
IF(
    ISBLANK(_retult),
    0,
    _retult
)
CountAlertB = 
var _result = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Category] = "B"&&
            'Table'[AlertTrigger] = "Y"
        )
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)
CountAlertC = 
var _result = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Category] = "C"&&
            'Table'[AlertTrigger] = "Y"
        )
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)
CountAlertD = 
var _result = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Category] = "D"&&
            'Table'[AlertTrigger] = "Y"
        )
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)
CountAlertF = 
var _result = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Category] = "F"&&
            'Table'[AlertTrigger] = "Y"
        )
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

Then you may use card visuals to display the results and publish th report to service. You need to pin them to the dashboards and set an alert for each pinned visual. 

You may set alert rules and click 'Use Microsoft Power Automate to trigger additional actions' to send an email to corresponding user.

Best Regards

Allan

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

[TCavins]

Thank you. Looks like this solution would work. Downside is if I have 50 categories, I'd have to have 50 measures and 50 cards. I may need to develop an outside solution in an SSIS package and see how I like that.

Thank you for the help.

[DataVitalizer]

Hi @TCavins 

I suggest you to follow the below steps:

1- create a measure to count #Y

2- add the measure to your report and duplicate it

3- filter each visual by selecting a specific category (Eg: Visual 1 represents #Y for categor A, Visual 2 represents #Y for categor B ...)

4- from Power BI Service pin these visuals to a new dashboard

5- from the dashboard, set an alerts for your different tiles representing #Y per category

6- make sure to uncheck the box send me the notification, you will be invited to use power automate to specifiy an email address ( Eg: for tile 1 send notification to BobSmith@someemail.com)

Does it work ? Mark it as a solution 

A kudos would be appreciated