cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
igaca
Helper III
Helper III

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 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

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Any thoughts?

igaca
Helper III
Helper III

Anyone? Smiley Happy

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!