Power BI provides slicers for single columns, but there are scenarios where it could be useful to consolidate alternative filters for multiple columns into a single slicer. Technically, this is not possible in Power BI through the standard visualizations, but you can use a particular data modeling technique to obtain the desired result.
Consider the case of a Customer table with a geographical hierarchy with Continent, Country, and State. The requirement is to enable a filter over California (State), France (Country), and Asia (Continent) using a single slicer, as shown in the following screenshot.
The first step required in order to implement the solution is to create a calculated column in the Customer table that defines the granularity for the filter. Because the State value may be repeated, we create a calculated column in the Customer table by concatenating State and Country. The Continent column is not required because each Country belongs to only one Continent – in a different situation, consider concatenating all the columns that define a unique value for each row of the table:
- Block diagram of the data-slicer blocks in the MAX1473, including external components. The Fundamental Data-Slicing Circuit Figure 4 shows the simplest data-slicing circuit. The output of the data filter, DFO, goes to the positive pin of the data slicer comparator, DSP, and also forms the slicing threshold voltage at DSN by passing.
- Learn Excel from MrExcel Podcast, Episode 2198: A Slicer for Two Data Sets. Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was in New Jersey doing a seminar there, and Rick asked a question, he said, 'Hey, look, I have pivot tables built on two different data sets and I'd like one slicer to be able to control those.'
- The Chiclet Slicer was inspired by the great slicer control found in Excel since 2010, but with much greater customization options. Chiclet are a slicers made of buttons, that can also be arranged horizontally for a very efficient real estate use, or arranged as a matrix for a super compact form.
The StateCountry column is needed to create a relationship with the Slicer table that shows the possible choices in a single item. Such a table has a Slicer[Selection] column with at least one value for each item displayed in the slicer, and all the combinations of StateCountry values to filter for each possible selection:
There has to be a relationship in the Slicer table obtained this way, between Slicer[StateCountry] and Customer[StateCountry]. This relationship has a many-many cardinality and a single filter direction, so that Slicer filters Customer (and not the other way around).
Bottom line: Learn how to create this interactive chart where the data label metrics change based on a slicer. Skill level: Advanced I have written before about how I am NOT a big fan of stacked charts, and shared some alternatives to the stacked bar or column chart. Basically, it's hard to make comparisons between the series in the bars because of the uneven baseline.
Now the model is ready. By selecting an item in the slicer, you apply a filter to the Slicer[Selection] column. This filter automatically propagates to the Customer[StateCountry] column thanks to the relationship created in the previous step. This is the model that supports the report shown at the beginning of this article.
Clearly, Slicer[Selection] could be used as a regular column in a visual. The following screenshot shows a matrix where the sales in Australia are included in two rows: Asia and Australia. Whenever you have a many-to-many cardinality relationship involved in a report, the measure is non-additive and the total shows a number that is not the sum of the visible rows – the total corresponds to the cumulative amount of the states selected considering each state only once.
This solution should work well for models where the cardinality of the relationship is of a few hundred unique values. If the Slicer[StateCountry] column has thousands of unique values or more, then it is better to consider an approach based on physical strong relationships relying on an intermediate table created with the unique values of Slicer[StateCountry]. The following technique is also available in Analysis Services versions that do not support the many-to-many cardinality in relationships. You can create the intermediate table StateCountry using the following calculated table definition:
By enabling the bidirectional filter between the Slicer and StateCountry tables – which should be hidden in report view – you get the same result of a many-to-many cardinality relationship with improved performance.
The technique described in this article is useful whenever you want to show in a single column the values that are natively present in different columns of a table. This provides a better user experience and a simplified user interface of the report.
This document will describe step by step procedure to implement Data Slice lock using BPC embedded Technology. This concept has been described using BPC 11 Embedded and BW4HANA technology.
- Administrator can Lock / Unlock Data via Analysis Office Template by clicking one button.
- Once Data Selection is being locked by administrator, no one can plan or post any data for that locked selection.
- Once Data is being unlocked by administrator, posting / planning can be possible within that unlocked data selection.
We assumed a generic architecture, which can be used by any planning scenario.
Normally for any Embedded Planning scenario, we have two distinct data flows. One is normally coming from BW Actual Data which is denoted in below diagram as ACTUAL – Data aDSO. The PLAN – Data aDSO denotes the Planning related data to be posted in this aDSO. The composite provider joins both Actual and Plan data. The Aggregation Level – Plan has been created on the top of Composite Provider and the Planning Query has been created on the top of Aggregation level. The Planning Sequence has been created on the top of Aggregation Level.
We introduced another aDSO, named LOCK aDSO which will store the Lock unlock information. The lock and Unlock will be two planning functions one will make an entry with some Flag value as Locked and another planning function will change the Flag value as unlock. The Below figure shows the diagram.
Sometimes the Lock / Unlock information can be stored in within same Plan aDSO. The Composite provider can be created on the top of all 3 aDSO’s: Actual aDSO, Plan aDSO and Lock aDSO.
The below figure shows the diagram.
In our example we have a transaction data for Actual and Planning as below.
We want the below mentioned selection is relevant for locking mechanism. For any entry with a flag value ‘1’ is locked and the same selection with flag value is ‘0’ for unlock.
Direct Update Planning aDSO to be created.
For storing the Lock / Unlock information we created a separate aDSO which is planning enable and it is also direct update in settings.
In our example, the below characteristic are the lock relevant.
We maintain the primary key as it is direct update aDSO.
Aggregation Level for lock to be created.
We need to create the Filter, Planning Function and Planning Sequence for Lock / Unlock Functionality. We added all lock relevant characteristic in Lock / Unlock Aggregation level.
Planning Filter for Lock / Unlock to be created.
We are going to use the same Planning filter for Lock operation and same planning filter for unlock operation. Normally administrator will first lock and then unlock for a particular selection.
In our example, Administrator will lock or Unlock based on Account and Posting Period Value.
Planning Function for Lock to be created.
We are going to create a planning function which will be created by FOX code. Dynamically we will read the planning filter value via FOX code and add the lock entry in direct update aDSO.
The FOX code shown below will generate an entry for lock. We are adding one entry with Flag value as ‘1’ which represent the Lock entry.
Planning Sequence for Lock to be created.
We will call this Lock function from Analysis Office Template, so we are going to create a planning Sequence.
Planning Function for Unlock to be created.
To unlock, we are going to modify the Flag value to ‘0’. We are going to create a Planning Function for unlock functionality.
See the settings.
The FOX cod would be as below.
Excel Slicer Data Model
Planning Sequence for Unlock to be created.
To call the planning sequence from Analysis Office Template, we need a planning sequence.
Analysis Office Work book to be created.
We are going to create Two Buttons in our example, one is Lock and one is Unlock. We need a have user selection where user wants to see whether the particular selection is locked or unlocked. The Template Looks like below.
If you click, lock button and entry for lock will be generated. If you unlock the button then same entry flag will be modified to ‘0’. Let’s press lock button and see the changes.
See the lock entry.
Let’s press the unlock button and see the changes.
Exit to be created.
This Exit class will read the value dynamically during planning posting in planning cube and stop posting data if the value is locked. Need to create a class and write the code to read the value from aDSO.
See the class and method code implemented.
To create the class and method, please follow the bellow link.
Data Slice to be activated.
Now the exit class to be linked with data slice where planning can be done.
Go to RSPLAN and select the PLAN aDSO name.
You can refer the below link for step by step creation.
Test the Data Slice.
4 Level Data Slicer Manual
We have a planning Query which will input data in our system. Our intention is to check the data slice working principle.
a) First step, we will push the data from input enable query.
We open the input enable query and we have seen it is input enable so we can change the data.
b) Second Step, we will lock the selection from administrative template.
4 Level Data Slicer As Seen On Tv
Now we are going to lock the data via administrative template.
c) Third step, Try to push data in system via same input enable query. The posting of data is not allowed.
4 Level Data Slicer Tool
To lock the mentioned selection, we run the lock button providing the same selection.
We do not have any values as we have not pressed the button. Press the Lock button so the lock entry will be generated.
See the locked entry is grayed out, that means the input is not possible. Planning is not available for this selection.
d) Last step, unlock the data selection and try to post the data again via query.
4 Level Data Slicer Attachment
Unlock the data via Administrative template.
Data Slicers Excel
The unlock functionality worked, which means that the posting from input enable query is now possible.