Filter by values NOT selected

[igaca]

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 the entire organization.  I've created a dimension table with a unique list of all software applications and would like to show -in another table- a listing all computers which DO NOT have the software application/applications selected via the dimension table slicer.

My initial thought was to create a calculated table utilizing the EXCEPT() function and the dimension table slicer inputs (is dynamic), then propagates those selections to the fact table via a relationship (the particular dimension has a large cardinality/lots of different software apps used across the organization) but I've hit a wall along these lines.  I've also tried a couple of alternate approaches but those did not yield the desired results either.

I'd love to hear what creative ways the members of this community would suggest in resolving this type of a problem.  Thanks for your input in advance!

Cheers,

Igor

[v-shex-msft]

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]

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!

[v-shex-msft]

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

[igaca]

Any thoughts?

[igaca]

Anyone?