Power BI: how to unslice your data

This is a very interesting use of power bi Slicer. The original article is here. it’s writed by Art Tennick. Here the complete article:

A normal slicer can be tedious when you want to show everything apart from just one or two entries in your filtered tiles – don’t take your finger off the Ctrl key! You could always turn on Select All, then unselect the items. But you may not want Select All enabled, and it’s not available for chiclets. Or you could use Visual/Page/Report level filters, but these are not available in dashboards or publish-to-web. So you may be interested in an anti-slicer? There are many ways to do this, this is one approach.Immagine 035.png

If you want to reproduce my example you need to import DimGeography, DimCustomer, and FactInternetSales from Adventure Works DW. Check all three tables are related. Then make a copy of DimGeography in Power Query and rename it to Country. Make sure it has no relationships to any other table. The normal chiclet slicer is based on DimGeography, the two chiclet anti-slicers are based on Country. All three use the EnglishCountryRegionName column. Finally, add the DAX measures shown below (the last three are optional) and build the three tiles (as per the screenshots, and use EnglishCountryRegionName from the DimGeography table not the Country table):

Sales = SUM(FactInternetSales[SalesAmount])

Anti-Sales (single) = CALCULATE([Sales], FILTER(DimGeography, ‘DimGeography'[EnglishCountryRegionName] <> VALUES(Country[EnglishCountryRegionName])))

Number countries to show = COUNTROWS(ALL(‘Country'[EnglishCountryRegionName])) – COUNTROWS(VALUES(Country[EnglishCountryRegionName]))

Anti-Sales (multiple) = IF([Number countries to show] = 0, CALCULATE([Sales]), CALCULATE([Sales], EXCEPT(VALUES(DimGeography[EnglishCountryRegionName]), VALUES(Country[EnglishCountryRegionName]))))

Slicer value/s = CONCATENATEX(VALUES(DimGeography[EnglishCountryRegionName]), DimGeography[EnglishCountryRegionName], “, “)

Anti-slicer single value = IF(NOT(ISBLANK([Anti-Sales (single)])), CONCATENATEX(VALUES(Country[EnglishCountryRegionName]), Country[EnglishCountryRegionName], “, “))

Anti-slicer multiple value/s = IF(NOT(ISBLANK([Anti-Sales (multiple)])), CONCATENATEX(VALUES(Country[EnglishCountryRegionName]), Country[EnglishCountryRegionName], “, “))


Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...