Forum Discussion

Ania26's avatar
Ania26
Helper IV
1 year ago
Solved

Remove blanks from Parameter Fields

Hello ,   I have Paramter: Parameter = {     ("Incident Type", NAMEOF('Incident Input'[Incident Type]), 0),     ("Incident Year", NAMEOF('Incident Input'[Incident Year]), 1),     ("Law Enforce...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Ania26 

    Create a table with a single column that will act as a toggle for the filter:

    FilterToggle = DATATABLE(
    "Filter", STRING,
    {
    {"On"},
    {"Off"}
    }
    )

     

    Create a measure that checks the value of the parameter and applies the filter conditionally:

    ApplyFilter =
    IF(
    SELECTEDVALUE(FilterToggle[Filter]) = "On",
    IF(
    NOT(
    ISBLANK('Incident Input'[Incident Type]) ||
    ISBLANK('Incident Input'[Incident Year]) ||
    ISBLANK('Incident Input'[Law Enforcement Agency])
    ),
    1,
    0
    ),
    1
    )

     

    Add a slicer to your report using the FilterToggle table.
    Drag the ApplyFilter measure to the Filters pane of your visual.
    Set the filter to show only values where ApplyFilter is 1.

Ania26's avatar
Ania26
Helper IV
1 year ago

Hello, I have below error: A single value for column 'Subject State' in table '* Subject State' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Ania26 , Try using

DAX
ApplyFilter =
IF(
SELECTEDVALUE(FilterToggle[Filter]) = "On",
IF(
NOT(
ISBLANK(MIN('Incident Input'[Incident Type])) ||
ISBLANK(MIN('Incident Input'[Incident Year])) ||
ISBLANK(MIN('Incident Input'[Law Enforcement Agency]))
),
1,
0
),
1
)