Power BI Chart Filtering Issue with SELECTEDVALUE & Field Parameters

[jaykay3000]

Hi!

In PBI desktop I have the following model:

The model consists of Financial Data needed for creating a PnL report, along with corresponding dimension tables.

The 'DIM Account' table is set up in this way:

My goal is to create a fully functional, dynamic bar chart that allows users to:

1. Select the Y-axis breakdown using Slicer A.
2. Filter corresponding accounts (CM, EBIT, EBITDA, etc.) using Slicer B.

Slicer A (Y-Axis Breakdown - Field Parameter)

I created the following Field Parameter, which is used as the Y-axis field in my visual:

Breakdown by: = {

    ("Month Year Name", NAMEOF('DIM Report Date'[Month Year Name]), 4),

    ("Location", NAMEOF('DIM Location'[Location]), 5),

    ("Coast", NAMEOF('DIM Location'[Coast]), 6),

    ("Level 1", NAMEOF('DIM Account'[Level 1]), 1),

    ("Level 2", NAMEOF('DIM Account'[Level 2]), 2),

    ("Level 3", NAMEOF('DIM Account'[Level 3]), 3)

}

Slicer B (Account Filtering - Custom Flag Table & Measure)

I created a separate table, _FlagChoice, and a corresponding measure FilterBySelectedFlag to filter the graph based on the selected PnL level.

Here’s the FilterBySelectedFlag measure used to filter the chart (FilterBySelectedFlag = 1):

FilterBySelectedFlag =

VAR SelectedFlag = SELECTEDVALUE( '_FlagChoice'[FlagChoice], "None" )

VAR RevenueVal = SELECTEDVALUE('DIM Account'[Revenue flag])

VAR ContribMarginVal = SELECTEDVALUE('DIM Account'[Contribution Margin flag])

VAR ProductionEbitdaVal = SELECTEDVALUE('DIM Account'[Production EBITDA flag])

VAR OperatingEbitdaVal = SELECTEDVALUE('DIM Account'[Operating EBITDA flag])

VAR EbitdaVal = SELECTEDVALUE('DIM Account'[EBITDA flag])

VAR EbitVal = SELECTEDVALUE('DIM Account'[EBIT flag])

VAR PatVal = SELECTEDVALUE('DIM Account'[PAT flag])

RETURN

IF(

    // If no slicer selection is made, show everything

    SelectedFlag = "None",

    1,

    IF(

        // OR logic: row matches if the row's column = the selected flag 

         ( SelectedFlag = "Revenue" && RevenueVal = "Revenue" )

        || ( SelectedFlag = "Contribution Margin" && ContribMarginVal = "Contribution Margin" )

        || ( SelectedFlag = "Production EBITDA"   && ProductionEbitdaVal = "Production EBITDA" )

        || ( SelectedFlag = "Operating EBITDA"  && OperatingEbitdaVal = "Operating EBITDA" )

        || ( SelectedFlag = "EBITDA"            && EbitdaVal = "EBITDA" )

        || ( SelectedFlag = "EBIT"              && EbitVal = "EBIT" )

        || ( SelectedFlag = "Profit after tax"               && PatVal = "Profit after tax" ),

        1,

        0

    )

)

That allowed me to partially accomplish my goal:

But the report returns no results when 'Breakdown by:' is set to:

• Month Year Name
• Location
• Coast

 

I suspect the issue is related to SELECTEDVALUE() and how the FilterBySelectedFlag measure is being calculated in the context of the 'DIM Report Date'[Month Year Name],  'DIM Location'[Location] and 'DIM Location'[Coast]. Since those fields are used in the Y-Axis, the filtering may not be correctly applied.

What would be the best approach to achieve correct filtering in this scenario?

here is the public github repo with the code: https://github.com/jaykay3000/PnLissue

here’s a temporary download link with the .pbix file: https://limewire.com/d/e482f168-f8b5-422e-a394-f791be4998a7#DxSJcMkyqmlHAq0k-QTIJKWE_gWnKTySxsC1qdx-...

Thanks!

[jaykay3000 · Accepted solution]

to anyone interested I managed to get it working, link here:

https://community.fabric.microsoft.com/t5/Desktop/Power-BI-Slicer-Based-Y-Axis-amp-PnL-Filtering-Not...

[Anonymous]

Hi, @jaykay3000 

You can try replacing dynamic flags with calculated columns. Precompute a fixed filter field, such as [Flag], in the DIM Account table to combine multiple flags into a single column:

Flag =
SWITCH(TRUE(),
'DIM Account'[Revenue flag] = "Revenue", "Revenue",
'DIM Account'[Contribution Margin flag] = "Contribution Margin", "Contribution Margin",
'DIM Account'[Production EBITDA flag] = "Production EBITDA", "Production EBITDA",
"Other"
)

Establish a relationship between _FlagChoice[FlagChoice] and DIM Account[Flag] (one-way filtering). Or use relational filtering without the need for complex DAX logic.

If you can't modify the model, you can adjust the measurement logic to check if the account associated with the current context contains a check flag:

FilterBySelectedFlag =
VAR SelectedFlag =
    SELECTEDVALUE ( '_FlagChoice'[FlagChoice], "None" )
RETURN
    IF (
        SelectedFlag = "None",
        1,
        IF (
            SelectedFlag
                IN CALCULATETABLE ( VALUES ( 'DIM Account'[Flag] ), ALLSELECTED ( 'DIM Account' ) ),
            1,
            0
        )
    )

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[jaykay3000]

Thanks for taking the time to answer!

Flag =
SWITCH(TRUE(),
'DIM Account'[Revenue flag] = "Revenue", "Revenue",
'DIM Account'[Contribution Margin flag] = "Contribution Margin", "Contribution Margin",
'DIM Account'[Production EBITDA flag] = "Production EBITDA", "Production EBITDA",
"Other"
)

But would this work if I have multiple flags for some 'DIM Account'[Account]? 

For example for DIM Account'[Account] = "SMKT1" I have flag for "Operating EBITDA", "EBITDA", "EBIT" and "Profit after tax". 

Wouldn't the SWITCH() just take the first fitting flag? So, for DIM Account'[Account] = "SMKT1", probbably "Operating EBITDA". Then, when the filter would be seto to "EBIT", the  DIM Account'[Account] = "SMKT1" would not be taken into account.