Using a Parameter Table with a Measure?

[BradOrders]

Hi all

I am still new to Power BI, and one issue is driving me crazy. I hope you can help ...

I am using a Parameter Table to let the user select a "% Warning Level".
When the Warning Level is selected, I want to display a message in every row in a table where a column value is greater than the selected Warning Level. So for example, if the user selects "200%", then every record with a percent greater than 200% will have a text message displayed in the Measure Column saying "Exceeds Level".

From my understanding, I can't use a Calculated Column, as I want the values to recalculate whenever the user selects a different Warning Level, and Calculated Columns are only recalculated when the data is refreshed.
Whenever I use a Measure, I need to use an aggregator, or I get a "single value cannot be determined" error. If I DO use an aggregator, then the value is not calculated on every row, just the subtotals.

Here is something like what I am trying to achieve:

M_Warning =
IF(
HASONEVALUE(WarningLevels[Warning Level 1]),
IF(SignificantDifference[Change] > VALUES(WarningLevels[Warning Level 1]), "Exceeds Level", "")
,"")

Any advice would be greatly appreciated ...

Thanks!

Brad

[BradOrders]

Thanks Herbert, that was a great, thorough answer.  Just what I needed ....  🙂

[v-haibl-msft · Accepted solution]

@BradOrders

In this scenario, you can add an Index column for the “SignificantDifference” table and then make a small change to your measure formula. Please refer to following steps.

1. Add an Index column in Query Editor.

2. Modify your measure formula as below.

M_Warning = 
IF (
    HASONEVALUE ( WarningLevels[Warning Level 1] ),
    IF (
        CALCULATE ( SUM ( SignificantDifference[Change] ) )
            > VALUES ( WarningLevels[Warning Level 1] ),
        "Exceeds Level",
        ""
    ),
    ""
)

 3. Drag a Slicer and Table chart into your canvas.

Best Regards,

Herbert