Forum Discussion

charonT's avatar
charonT
Helper I
2 years ago
Solved

Remove blank data after selecting the parameter slicer

Hi, I am a new user of Power BI and I might not have explained my issue clearly. I've set up a parameter that enables users to choose the column they want as the x-axis for a graph. However, there are empty cells in the data, and I want to exclude these blank values from the graph when users select the respective field. I attempted using the filter pane, but it seems to affect other fields as well when I set Is not Blank for the field I selected. Do you have any suggestions on how to solve this issue?

Many thanks.

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    charonT,

     

    Create a measure using the pattern below. The SWITCH function should have an entry for each column in your field parameter. Add this measure as a visual filter (greater than 0).

     

    Visual Filter =
VAR vSelectedParameter =
    SELECTCOLUMNS (
        SUMMARIZE ( 'Parameter', 'Parameter'[Parameter], 'Parameter'[Parameter Fields] ),
        "Parameter", 'Parameter'[Parameter]
    )
VAR vResult =
    SWITCH (
        vSelectedParameter,
        "ACTMEAN",
            SUMX (
                DimTable,
                VAR vColumn = DimTable[ACTMEAN]
                RETURN
                IF ( NOT ISBLANK ( vColumn ), 1 )
            ),
        "ACTONTRACK",
            SUMX (
                DimTable,
                VAR vColumn = DimTable[ACTONTRACK]
                RETURN
                IF ( NOT ISBLANK ( vColumn ), 1 )
            )
    )
RETURN
    vResult

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    charonT,

     

    Create a measure using the pattern below. The SWITCH function should have an entry for each column in your field parameter. Add this measure as a visual filter (greater than 0).

     

    Visual Filter =
VAR vSelectedParameter =
    SELECTCOLUMNS (
        SUMMARIZE ( 'Parameter', 'Parameter'[Parameter], 'Parameter'[Parameter Fields] ),
        "Parameter", 'Parameter'[Parameter]
    )
VAR vResult =
    SWITCH (
        vSelectedParameter,
        "ACTMEAN",
            SUMX (
                DimTable,
                VAR vColumn = DimTable[ACTMEAN]
                RETURN
                IF ( NOT ISBLANK ( vColumn ), 1 )
            ),
        "ACTONTRACK",
            SUMX (
                DimTable,
                VAR vColumn = DimTable[ACTONTRACK]
                RETURN
                IF ( NOT ISBLANK ( vColumn ), 1 )
            )
    )
RETURN
    vResult