CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Serdia
Serdia

Reputation: 4428

How to change value in column based on the other table that is not related

I have a table with column num that contain numbers from 10,000 to 1,000,000. This table need to be used as slicer.

Second Table contains ClaimNumber and LossAmount per each claim.

enter image description here

How can I generate a new dynamic column (or measure) that would decrease LossAmount per ClaimNumber based on number chosen in a slicer. Only, of course, if Loss Amount is greater than the num column.

For example if I chose 40,000 then all Claims that have LossAmount more than 40,000 need to be equal 40,000.

On a picture below you see, that choosing 40,000 cap Claim5 and Claim7 became 40,000.

Is it possible to achieve this?

enter image description here

Upvotes: 0

Views: 46

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You definitely need to do this as a measure since a calculated column cannot be responsive to slicers.

The measure should be fairly simple.

Capped Loss Amount =
VAR MaxLoss = MAX(Slicer[num])
RETURN
SUMX(
    Claims,
    IF(
        Claims[LossAmount] > MaxLoss,
        MaxLoss,
        Claims[LossAmount]
    )
)

Upvotes: 1

Related Questions