Dynamic Table with field parameter and two value slicers partially working.

[jctasu]

Hello Everyone.

I need some help to cross the finish line on a visual and was hoping it would be an easy fix with some guidance from the community.

I have a matrix table visual as shown below. 

    

I want this table to update dynamicaly based on the CV and SV buttons.  So far I have been able to do that.  I also want it to update dynamically based on the negative and/or positive limit that the user selects in the negative and positive limit slicers.  So far I have been able to accomplish this partially with the CV selection, but not the SV selection.  Below example shows the CV table accurately change based on the limit slicers, but when changed to the SV view the table is still basing results off of the CV measure.  Measures for columns shown as 1 on the table are shown below (IncludeValueCV, IncludeValueSV).

IncludeValueCV = IF(

    [CV Cur] < SELECTEDVALUE('Lower Limit'[Lower Limit]) ||

    [CV Cur] > SELECTEDVALUE('Upper Limit'[Upper Limit]),

    1,

    0

)

IncludeValueSV = IF(

    [SV Cur] < SELECTEDVALUE('Lower Limit'[Lower Limit]) ||

    [SV Cur] > SELECTEDVALUE('Upper Limit'[Upper Limit]),

    1,

    0

)

Here are is the parameter and values that are in the matrix table.

prm_variance_options = {

    ("CV", NAMEOF('fct_ev_data'[CV]), 0, "CV"),

    ("1", NAMEOF('fct_ev_data'[IncludeValueCV]), 1, "CV"),

    ("SV", NAMEOF('fct_ev_data'[SV **bleep** EOM]), 0, "SV"),

    ("1", NAMEOF('fct_ev_data'[IncludeValueSV]), 1, "SV")

Here are the parameters for the lower and upper limit slicers.

Lower Limit = GENERATESERIES(-5000000, 0, 50000)

Lower Limit Value = SELECTEDVALUE('Lower Limit'[Lower Limit])

Upper Limit = GENERATESERIES(0, 5000000, 50000)

Upper Limit Value = SELECTEDVALUE('Upper Limit'[Upper Limit])

I appreciate any guidance the community can provide in getting both CV and SV values in the matrix table when updating the lower and upper limit value slicers.

Thank you.

Joaquin C.

[Anonymous · Accepted solution]

Hi @jctasu ,

When switching slicers, because you have two 1 is in the field parameter, you may not be able to correctly recognize which 1 is which when switching and use the correct parameter.

The solution is to create a calculated column that references the column in the Filed parameter that applies to the slicer, and then reference that calculated column in the measure syntax.

As you can see below, this is the simple field parameter I created:

Create a calculated column:

Create a measure:

Measure =
VAR _cv =
    IF (
        [CV Cur] < SELECTEDVALUE ( 'Lower Limit'[Lower Limit] )
            || [CV Cur] > SELECTEDVALUE ( 'Upper Limit'[Upper Limit] ),
        1,
        0
    )
VAR _sv =
    IF (
        [SV Cur] < SELECTEDVALUE ( 'Lower Limit'[Lower Limit] )
            || [SV Cur] > SELECTEDVALUE ( 'Upper Limit'[Upper Limit] ),
        1,
        0
    )
RETURN
    IF ( MAX ( 'Parameter'[Column] ) = "CV", _cv, _sv )

Apply measure to your visual objects.

If this does not solve your problem, please provide a Power BI Desktop file (with sensitive information removed) that comprehensively covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set to public access), SharePoint, or a Github repository and then share the URL of the file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!