Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
)
)
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
)
)
)
@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
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 I am using a star schema design. I continue to face two challenges with this model.
Am I missing something with your response?
I am currently exploring @amitchandak independent table suggestions.
Regards.
@Anonymous If you are looking for a measure using unselected values using Independent table
https://www.youtube.com/watch?v=lOEW-YUrAbE
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |