Dynamic constant line for scatter plot based on parameter values

[xariet]

Is it feasible to create a dynamic constant line based on the values of a field parameter? Suppose I have a scatter plot with Annual Income on the x-axis and a Field parameter called "Pick measure" on the y-axis,

Pick measure = { ("LTV ratio", NAMEOF('Sheet1'[LTV ratio]), 0), ("Borrower Debt to Income Ratio", NAMEOF('Sheet1'[Borrower Debt to Income Ratio]), 1), ("Amount Borrowed", NAMEOF('Sheet1'[Amount Borrowed]), 2) }

My goal is to have the constant line's values adjust based on the selected measure. For instance, if I select "LTV ratio," the constant line should display a value of 80%, while selecting "Borrower Debt to Income Ratio" should result in a constant line value of 40%, and selecting "Amount Borrowed" should produce a value of $300,000.

I have created measure

choose Value = IF(

    SELECTEDVALUE('Choose measure 2'[Pick measure])="LTV ratio",

    40,

    IF(

        SELECTEDVALUE('Choose measure 2'[Pick measure])="Borrower Debt to Income Ratio",

        80,

        IF(

            SELECTEDVALUE('Choose measure 2'[Pick measure])="Amount Borrowed",

            150000,

            0

        )

    )

)

but it doesn't work

Frown (Error)

Error message:
MdxScript(Model) (27, 5) Calculation error in measure 'Annual Income'[choose Value]: Column [Pick measure] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

[xariet]

Thank you Aniya! To push it further, is it possible to include in this calculation a parameter to choose the values instead of fixed values? for example, if I choose the LTV ratio I want to have a choice of values, not just 40 as in the formula?

[v-yueyunzh-msft]

Hi, @xariet 

You can try to create a new Table[column] as the value slicer and you can put the column in a slicer and you can replace the MAX(Table[column]) with the 40 value in your dax measure.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft · Accepted solution]

Hi , @xariet 

According to your description, you want to add a constant line in your visual and you get an error code .

I test it in my side , when using your dax code , i get the same error in my side .

For this , it seems we can not use the SELECTEDVALUE() function to get the slicers' selection.

You can try to use this dax code and we can meet your need:

choose Value = IF(
     MAX('Choose measure 2'[Pick measure])="LTV ratio",
    40,
    IF(
        MAX('Choose measure 2'[Pick measure])="Borrower Debt to Income Ratio",
        80,
        IF(
            MAX('Choose measure 2'[Pick measure])="Amount Borrowed",
            150000,
            0
        )
    )
)

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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