topic Re: Using SELECTEDVALUE slicer and returning all results if nothing is selected in DAX Commands and Tips

Using SELECTEDVALUE slicer and returning all results if nothing is selected

Anonymous — Mon, 22 Mar 2021 23:09:00 GMT

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.

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
            )
        )

Re: Using SELECTEDVALUE slicer and returning all results if nothing is selected

amitchandak — Tue, 23 Mar 2021 03:44:49 GMT

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

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

Re: Using SELECTEDVALUE slicer and returning all results if nothing is selected

Anonymous — Tue, 23 Mar 2021 13:26:13 GMT

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.

Re: Using SELECTEDVALUE slicer and returning all results if nothing is selected

Anonymous — Tue, 23 Mar 2021 13:54:32 GMT

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

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.)
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.

Re: Using SELECTEDVALUE slicer and returning all results if nothing is selected

Anonymous — Tue, 23 Mar 2021 16:13:21 GMT

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

Re: Using SELECTEDVALUE slicer and returning all results if nothing is selected

Anonymous — Thu, 25 Mar 2021 03:04:04 GMT

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
        )
      )
    )