Altering “OR” to help you “AND” Reason to have Power BI Slicers
Goal: Create the expected computed fields to evolve “OR” so you’re able to “AND” reason to have Strength BI slicers (local slicer additionally the hierarchySlicer).
I’ve been having fun with Electricity BI getting half a year now. Eg early in the day BI units I have used, a couple of hacks make it possible to alter seemingly impossible business requirements toward super pleased reactions:
A few months ago, I was asked to improve the new logic in this a personalized visual, the brand new hierarchySlicer, of appearing study predicated on “OR” logic in order to indicating study based on “AND” logic. After some investigating and several efforts, I am pleased you to the necessary hack has been discovered!
Borrowing the words away from Rob 
Collie: The greater number of you select, the more your “get”. Since you pick a lot more contents of your slicer, Strength BI suggests the commitment of one’s data or perhaps the distinctive line of set of activities met because of the deciding on the slicer things. With every extra slicer items chosen, the entire bits of data chopped grows.
Whatever the organization needs will be, at the conclusion of the afternoon we should figure out which members from inside the a group enjoys well-known enjoys. You want to perform an intersection of your study. Thus what if which i am wanting understanding and this countries’ flags act like both predicated on colour and you will information. We’ll introduce this which have a couple different Energy BI slicers:
If the belongings in the newest slicer was selected and also the level of things picked into slicer try equal to just how many selected parameters to have a nation, let you know the fresh places
- Native slicer
- hierarchySlicer
I wish to getting clear how the info try arranged to possess replicating this process. I restructured UCI’s Flags Research Set to your a couple more dining tables, one holding the nation suggestions (countryInfo) and also the most other carrying the latest banner pointers (flagInfo). countryInfo’s prie of the country and you can very important metrics eg urban area, population, code, an such like. inhabit so it desk. The new varying and you can outline flag data is a hierarchical framework, and therefore lives in flagInfo. countryInfo and you can flagInfo provides a-one to numerous relationships into title of the country. Whenever repeating it to suit your study lay, setup a comparable build to ensure best show.
When the belongings in the fresh new slicer were chosen and the quantity of affairs chose to your slicer was equivalent to exactly how many chose details having a country, let you know the brand new countries
- Counts just how many situations chose regarding the slicer
Description: In the event the no things was chose throughout the slicer, let you know all regions. If items in new slicer was basically chose therefore the quantity of issues selected on the slicer try equivalent to what amount of chosen colors to own a nation, reveal the brand new places.
Description: Filter the world table merely in which PBISlicer Take a look at is valid. Toward filtered table, number this new rows of the table.
When creating the real dashboard, you will want to make sure that the latest slicer’s “AND” reasoning outcomes all the dashboard design.
It is important to remember for this example would be the fact i provides a couple tiered logic when you look at the hierarchy. On the top peak, And reasoning can be applied (internet explorer. Require countries’ flags where info was symbol Otherwise information was residence). We will perform four computed steps that may list of positive actions:
If the items in the slicer were chose additionally the quantity of points chosen into the slicer is equal to the number of chosen variables getting a nation, inform you the brand new regions
- Matters just how many factors chose from the slicer
Description: Filter the nation table only where HierarchySlicer Look at is valid. Towards blocked desk, number new rows of your own table.
Description: Filter the country desk merely where HierarchySlicer Have a look at is valid. Into blocked desk, estimate the full total populace.
When making the real dash, you will want to ensure that the slicer’s “AND” reason consequences all dash visuals. Refer to the above mentioned instance to manufacture the last card and you will table visualizations.
- I would not endorse that have several ‘AND’ logic slicers on the same dashboard, understandably the latest logic to your ‘Check’ DAX computations can be extremely complicated very fast.
- As well as, I might not recommend that have numerous ‘AND’ reason slicers for a passing fancy dashboard as this can lead to less than perfect show when i got found while using the dos ‘AND’ logic hierarchySlicers in identical dashboard.
- Carry out lots of quality-control on your research overall performance!
Want countries’ flags in which you will find info And band), whenever you are at the end level, Otherwise reasoning applies (internet explorer
There are numerous other ways as you are able to use the new ‘AND’ logic DAX equations (internet explorer. having fun with variables, breaking the computations upwards a lot more, an such like.). This is actually the method that struggled to obtain myself and you can scaled better for the creation. The data, investigation reorganizing code, and you can PBIX document on the post is available right here. When you yourself have any queries otherwise applying for grants new tutorial, please touch base about comments below or as a result of Fb. Also, if you’d like for more information on Seismic Software and you can how exactly we play with Microsoft’s Stamina BI, check us out.
