Forum Discussion

megskilton's avatar
megskilton
Frequent Visitor
9 years ago
Solved

Creating table visualisations showing inverse filtering results

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 sho...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
aeh7576
New Member
9 years ago

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