Forum Discussion

igaca's avatar
igaca
Helper III
7 years ago

Filter by values NOT selected

Greetings!   I am looking for a way to filter a fact table by values which are NOT selected.   The scenario:  I have a dataset which reflects software applications by computer, user, etc, across ...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi igaca ,

Power bi not support to create dynamic calculated column/table based on filter/slicer, please write measure formula to instead.

AFAIK, current power bi not support to create 'not' relationship. I'd like to suggest you create a measure and apply on correspond visuals to filter records.

Sample:

 

Measure =
VAR curr =
    SELECTEDVALUE ( Table[Column] )
VAR unselected =
    EXCEPT ( ALL ( Table[Column] ), ALLSELECTED ( table[Column] ) )
RETURN
    IF ( curr IN unselected, "Y", "N" )

 

Regards,

Xiaoxin Sheng

igaca's avatar
igaca
Helper III
7 years ago

I can see instances (other than this particular example) where having the ability to filter by values NOT selected can be useful so perhaps this will be a future add for the BI team?

 

I see what you are trying to do with the measure but it is not working on my end.

 

Model Layout:

1) Dimension Table "*Applications" - Contains all unique values of "Application" column of the fact table titled "Report".

2) Fact Table "Report" - is linked via many-to-one relationship to the "*Applications" dim table ( 'Report'[Application] *-->'*Applications'[Application] ) 

 

Report Page:

Have a slicer based on the '*Applications'[Application] column

Have a table visual showing 'Report'[Computer] and 'Report'[Application] columns.

 

Measure titled "Visual Filter" placed on the table visual noted above, with criteria of "Show items when the value: ... is ... N.  The measure has the following syntax:

 

Visual Filter Measure =
    VAR curr =
            SELECTEDVALUE('*Applications'[Application])
    VAR unselected =
            EXCEPT(ALL(''*Applications'[Application]),ALLSELECTED('*Applications'[Application]))
    RETURN
            IF(curr IN unselected,"Y","N")
 

The intent of only displaying values not fitting the measure return criteria "N"  (unselected values) is not working as implemented above.  Let me know if there is something I am missing.

 

Thanks!

  • igaca's avatar
    igaca
    Helper III
    7 years ago

    Any thoughts?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi igaca ,

    You need to use measure on visual level filter to filter 'Y' result. My formula will extract opposite records of selection and compare with current row contents to return tag.

    If you confused how to use it, please provide a pbix file with some test data, then I can build a sample file to share.

    Regards,

    Xiaoxin Sheng