Creating table visualisations showing inverse filtering results

[megskilton]

I'm wanting to have 3 different slicers and two visualisation tables, the first two slicers will normally filter two tables containing the same data then the third filter will result in one table showing the filtered selection and the other showing results not in the filtered selection (Without including the filtered selection.) 

For example 

- Firstly Filtering by the region so only showing results from the desired selection, then the sub type and type from that region.

e.g. "Australia,Data centres,2"

This will make both Table visualisations the same. 

I then want to select the slicer 'fruit' and choosing Apple will show me in one table the list of "Australia,Data centres,2" with apples and the other will show the "Australia,Data centres,2" with Banana Pear and Plum (Or any data that isnt apples.)

Many Thanks 

[v-shex-msft · Accepted solution]

Hi @megskilton,

>>the third filter will result in one table showing the filtered selection and the other showing results not in the filtered selection (Without including the filtered selection.) 

I can use measure to get the filtered records and the inverted result, but I haven't find a way to enable the measure as the source of slicer.

filter value:

VALUES(Table[ColumnName])

inverted filtered value:

EXCEPT(ALL(Table[ColumnName]), VALUES(Table[ColumnName]))

Measure use to display these value:

filtered =
var temp= CONCATENATEX(VALUES(Sheet2[Amount]),[Amount]&",")
return
if(LEN(temp)>0,LEFT(temp,LEN(temp)-1),temp)

inverted filtered =
var temp= CONCATENATEX(EXCEPT(ALL(Sheet2[Amount]), VALUES(Sheet2[Amount])),[Amount]&",")
return
if(LEN(temp)>0,LEFT(temp,LEN(temp)-1),temp)

Regards,

Xiaoxin Sheng

[aeh7576]

Hello,

I've been working with Power BI for a while now, but I do not do any custom measures, however I need to do something similar to what was posted here. What's the correct DAX statement for selecting the inverse?

I would like for my slicer to select all values that do not equal (the inverse) the value I select. All my values are text and I'd like for this to show up in a table visual. So for example, my slicer selects retailers within a shopping center that are categorized as "book stores" and I'd like to see which shopping centers DO NOT have a category of book stores.

What is the exact measures for this? Table 1 has only categories and Table 2 has categories and several other attributes associated with it. Would I only use 2 measures? If so, could/can you provide what those measures look like and which measures belongs to which table and once I've created the measures where do I drag and drop each (for slicer and table visual.)

All suggestions and help is greatly appreciated.

Thank you,

Andy