Display records when the value of field paramater is blank or Zero

[TigerTony]

I have a table that has several records with about 40 columns.  I want to be able to show only the records that have a blank or Zero value for specific columns that are selected.

Parameters seems to be the best way to get my filtering but I am new to using this option.

E.g.

I created the Parameters below based on a measure that checks for blank items

_NoValueParameters = {
("Country", NAMEOF('_Measures'[_mCountry]), 1),
("E-Mail", NAMEOF('_Measures'[_mE-Mail]), 2),
("Type", NAMEOF('_Measures'[_mType]), 3),
}

_mCountry = IF(ISBLANK(SELECTEDVALUE(Cfile_Export[Country])),1,0)
_mE-Mail = IF(ISBLANK(SELECTEDVALUE(Cfile_Export[E-Mail])),1,0)
_mType = IF(ISBLANK(SELECTEDVALUE(Cfile_Export[Type])),1,0)

This is where I get Stuck

I then added the parameter to the Table Visual.  In the filter pane, I set the Paramater field to only show where the parameter field is blank or 1.  I repeated this for each of the parameters selected.

The output is not what is expected.

I created a new measure to check for the selected Parameter

_SelectedValue = SELECTEDVALUE(_NoValueParameters[_NoValueParameters Order])

and then added this to the table and check to see if this value is Blank or 1

Also, not what I am expecting.

I know the process is not as complicated as I am thinking it is.  Hoping someone can shed some light and assist.

[pmreis · Accepted solution]

Hi @TigerTony 

I think alternatively you can use a slicer table.

SlicerTable = 
DATATABLE(
    "FilterColumn", STRING,
    {
        {"Country"},
        {"E-Mail"},
        {"Type"}
    }
)

Then create a Dynamic Measure:

DynamicFilter = 
VAR SelectedColumn = SELECTEDVALUE(SlicerTable[FilterColumn])
VAR Result = 
    SWITCH(
        SelectedColumn,
        "Country", IF(ISBLANK(SELECTEDVALUE(Cfile_Export[Country])) OR SELECTEDVALUE(Cfile_Export[Country]) = 0, 1, 0),
        "E-Mail", IF(ISBLANK(SELECTEDVALUE(Cfile_Export[E-Mail])) OR SELECTEDVALUE(Cfile_Export[E-Mail]) = 0, 1, 0),
        "Type", IF(ISBLANK(SELECTEDVALUE(Cfile_Export[Type])) OR SELECTEDVALUE(Cfile_Export[Type]) = 0, 1, 0),
        BLANK()
    )
RETURN Result

Now you should be able to add a table visual to your report and include the columns you want to display. In the filter pane for the table visual, use the DynamicFilter measure and set it to show only the rows where the measure value is 1.

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

[TigerTony]

Pedro

This gave me exactly what I wanted.  Just had to made a change to the format of the OR to 

IF(OR(ISBLANK(SELECTEDVALUE(Cfile_Export[Country])) , SELECTEDVALUE(Cfile_Export[Country]) = 0), 1, 0),

Marking as Acepted Solution.  Thanks for the quick response.