Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using SELECTEDVALUE slicer and returning all results if nothing is selected

Hello,

 

I am using a slicer for my report legend and need to present all category options even if the data does not (yet) contain that scenario.

Here is an example of the slicer I am trying to populate.  Since we cannot color code slicer buttons (disappointing, please up vote my idea) you can see my current approach is dependent on all categories being included.

Slicer_Example.png

 

I am currently attempting to use a disconnected category table and SELECTEDVALUE with my measures.  I get the desired results if I make a selection.  However, I need to return all unfiltered results if no selection is made.

 

I found this article that described how to get all selected values from a slicer.  Is it possible to do something similar and define <alternateResult> as if all options were selected?... SELECTEDVALUE(<columnName>[, <alternateResult>])

Is there a better approach entirely to achieve my desired static slicer?

 

Below is a sample of my DAX.

 

Thank you,

 

Joe

 

'SelectedValue' is my unconnected table.

Total Hours = 
VAR selectedValue = SELECTEDVALUE( SelectedValue[Category Value] )

RETURN
CALCULATE(
[Hours] ,
FILTER(
ALL( 'Time Detail' ) ,
'Time Detail'[Category Value] = selectedValue
)
)

 

 

 

5 REPLIES 5
Anonymous
Not applicable

None of the responses resolved my issue, but below is the DAX I found that works.  The key was conditionally using the SELECTEDVALUE function ONLY IF there was a selection.

 

Thanks to those who responded or viewed my question.

 

Regards,

 

Joe

 

Total Hours = 

VAR selectedValue = SELECTEDVALUE( selectedValue[Category Value] )

RETURN
IF(
ISBLANK( selectedValue ) , // Checks if there is a slicer selection
CALCULATE( // If no slicer selection, routine SUM
SUM( 'Time Detail'[Total Hours] )
) ,
CALCULATE( // If slicer selection, uses SELECTEDVALUE to filter the [Category Value]
SUM( 'Time Detail'[Total Hours] ) ,
FILTER(
'Violation Detail' ,
'Category Detail'[Category Value] = selectedValue
)
)
)
Anonymous
Not applicable

@amitchandak Thank you for this direction.  It did help answer my original question, but also illustrated I overlooked the impact of my data structure... not all records have a Category.  Explicitly defining all Categories when none are selected, as your solution does, implicitly filters those records with no Categories.

 

In my presented DAX, [Hours] was a measure referencing my 'Data_ACTUAL' fact table, while 'Time Detail' relates to my (violation) Categories, which is a pivoted view since a single 'Data_ACTUAL' record could have more than one Category.

 

I think the independent table (I originally called in a disconnected table) is potentially not going to work because of all the underlying visual calculations.

 

Previously, I tried adding "dummy" records to my dataset and created all sorts of other unintended consequences.
Is there a way to define a slicer based on the underlying data, but append any categories missing from the dataset?
This approach would also mitigate cases where narrowly filtered scenarios would create missing Categories even if the Category exists in the (unfiltered) data.

 

Thank you for any further suggestions.

 

Regards,

 

Joe

Anonymous
Not applicable

@Anonymous 

 

If you followed the golden standard star schema design, you most likely wouldn't have to create a disconnected table for your slicer. And if your slicer were not disconnected (as it should not be), you wouldn't have to think about how to make your measure work as it would work out of the box, so to speak. To model your data properly, I'd recommend reading this: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

 

If you had a proper star schema, your measure [Hours] would automatically do what you want it to do. It would react to selections in your slicer naturally.

 

 

 

Anonymous
Not applicable

@Anonymous I am using a star schema design.  I continue to face two challenges with this model.

  1. Category scenarios not yet represented in the data do not present in the slicer
    Again, their absences is temporary and due to the reset of the accummulated data (for example, my week evaluations are not applicable until 7 days into the cycle.)
  2. The selection of a slicer option removes the other presented slicer options
    I am using the ChicletSlicer.

Am I missing something with your response?


I am currently exploring @amitchandak independent table suggestions.

 

Regards.

amitchandak
Super User
Super User

@Anonymous If you are looking for a measure using unselected values using Independent table

https://www.youtube.com/watch?v=lOEW-YUrAbE

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors