Forum Discussion
Slicer by logic conditions
Kindly I am asking if you have a smarter solution. Question is quite generic. Want to place a single selection slicer that has to show two options only, although the field includes many options.
Have implemented it by a separate table that lists the two options and a Measure with IF() that returns True according SELECTEDVALUE && content in the fact-table. So far so good. At this point, Power BI forces me to add the measure in the filter pane of the individual visuals (all boxes) that I have in the pages because the it does not accept a measure into the filter pane of the page. This is risky because there would not be easy evidence in case you forget to add the measure in the filter pane of a visual. Moreover, Cards do not accept the measure in their filter pane. Therefore, cannot be an option when a page include cards
How would you do it?
Column “Type” in Fact Table has values: “A”, “B”, “C”, “D” …
In the page that visualizes the fact table, a Single Selection Slicer for Column “Type” has to show two options: “A”, “All Content”. Of course, selection applies to all content of the page.
Thanks
Hi RRSSDW
To handle this kind of requirement with filter options consisting of overlapping sets, I would usually set up the model as follows (using your example):
- Creating a
Typedimension (if it doesn't already exist). - Create a
Type Filtertable containing the combinations of required filter options and correspondingTypevalues.Type Filter = "A"..."E"correspond to individualTypevalues of the same name, whileType Filter = "All Content"corresponds toType = "A"..."E". - Create bidirectional many-to-one relationship between
'Type Filter'[Type]andType[Type]. - Use
'Type Filter'[Type Filter]on the slicer. - Apply a page-level or report-level filter on
'Type Filter'[Type Filter]to limit the options as required, e.g."A"and"All Content".
In effect, this models a many-to-many relationship between
TypeandType Filter. This avoids having to handle the filtering via DAX.This is similar modelling used for overlapping date ranges in this blog post.
Small example PBIX attached.
- Creating a
6 Replies
- OwenAuger
Super User
Hi RRSSDW
To handle this kind of requirement with filter options consisting of overlapping sets, I would usually set up the model as follows (using your example):
- Creating a
Typedimension (if it doesn't already exist). - Create a
Type Filtertable containing the combinations of required filter options and correspondingTypevalues.Type Filter = "A"..."E"correspond to individualTypevalues of the same name, whileType Filter = "All Content"corresponds toType = "A"..."E". - Create bidirectional many-to-one relationship between
'Type Filter'[Type]andType[Type]. - Use
'Type Filter'[Type Filter]on the slicer. - Apply a page-level or report-level filter on
'Type Filter'[Type Filter]to limit the options as required, e.g."A"and"All Content".
In effect, this models a many-to-many relationship between
TypeandType Filter. This avoids having to handle the filtering via DAX.This is similar modelling used for overlapping date ranges in this blog post.
Small example PBIX attached.
- Creating a
- d_m_LNK
Super User
What are the slicers helping quantify on the page? I am guessing they help filter measures of somekind as they want to see totals for A only and then total for all types. My suggestion might be to take away the selection and just make two measures that total those specific things.
A Total = CALCULATE(MeasureName, Type = A)You could either just the next one make this the overall total or specify each type in a calculate:
All Content Total = CALCULATE(MeasureName, Type IN {A,B,C,D})
Not sure how many measures you have on the page but just a different way to see the same breakdown -- this way you would also be able to see those totals side by side and also create other comparison measure for the totals.
- RRSSDW
Helper I
Thanks for your replay. It is a prower bi report based on a fact table. The reports has multiple pages. Every page has multiple views (charts, tables, cards). the report uses many measures. The complete report can be applicable to full content of the table or to a subset. I want to force users to visualize all content of the pages based on full data (just like a "Select All" in a slicer) OR by selecting a specific subset (one a specific subset). The characteristic has many values. In a simple case, I should use a drop down or list slicer and the slicer would list all values (say 20 values) so that the user can select a value or unselect. Point is that I want to force the user on two only options: Select All; keep subset "A".
- Eric_jSosaFrequent Visitor
We use a parameter table + calculated column on the fact table, so the slicer acts as a native filter on the data (or conditional column in Power Query).
Step 1: Create the Parameter Table
Slicer_Type = DATATABLE("Option", STRING, "Order", INTEGER, {{"A", 1}, {"All Content", 2}})
Step 2: Create a Calculated Column in the Fact Table
Type_Slicer_Key = IF(FactTable[Type] = "A", "A", "All Content")
Or in Power Query as a Custom Column: if [Type] = "A" then "A" else "All Content"
Step 3: Relate Slicer_Type[Option] to FactTable[Type_Slicer_Key] with cardinality One to many (1:*).
Step 4: Use Slicer_Type[Option] as the slicer field.
- Kedar_Pande
Super User
keep your disconnected slicer table but handle the filtering logic inside your measures rather than the filter pane. Wrap every measure with the condition:
Sales =
VAR Selected = SELECTEDVALUE('SlicerTable'[Option])
RETURN
CALCULATE(
SUM('Fact'[Sales]),
IF(Selected = "A", 'Fact'[Type] = "A", ALL('Fact'[Type]))
) - RRSSDW
Helper I
Thank you all for your replies. Due to my specific purpose, the solution is the one from OwenAuger who has also better classified the purpose as " filter options consisting of overlapping sets". I have implemented it and it is working fine (Thanks).
Use Cases:
1) Instead of presenting a slices that has many values, I am forsing the users to a single selection that presents two only options: one of the possible values and a selection to keep all values, at which I can give a name (sort of rename of Select All in a normal slicer);
2) rename the "Select All" of a sliced and show it in a single selection: a dimension with two entries, A, B would be presented as A, B, Select All (optionally), Cancel Filter by a normal slicer. However, by the proposed methos it can be presented by a single selection slicer that has 3 options: A, B, All (which I can give a name). This is to transform a slicer in a single selection by including the Selsct All in the options of single selection.
Single Selection make it more stright forward for users and easier to interact with whenever few options and no need to make multiple selection (no need to combine selections from the same dimension during the enquires)