Forum Discussion

kylifeofpy's avatar
kylifeofpy
Frequent Visitor
2 years ago

Slicer Value Reference or Overriding Slicer

Hi PowerBI Community,

 

Tried searching through multiple forums and coming up with ways to go about it but can’t quite find a solution that works perfectly. I did come up with some ideas but unsure if it’s feasible, so let me know your thoughts. Both a screenshot and sample PBIX file from a public OneDrive is provided below for clarity. The file uses Import mode for troubleshooting, but my report is in Direct Query mode (more on that later).

 

Description: My report displays historical data and its pre-selected to display 2 dates as the default view. The table has 2 columns: ‘Day’ representing a number for a day and ‘Date’ the date of that day. Both is in reference to each other (e.g. 0 = 11/06/23, 1 = 11/05/23, etc). The pre-selection requirement must be from the ‘Day’ column because the database updates the data through that column only.

 

Issue: Due to the pre-selection requirement of slicing by the ‘Day’ column, having a date slicer for my end users limits their selection to 2 of 4 dates, but they should have the ability to select from all 4 dates.

 

Ideal Solution (Slicer Value Reference): If I continue to use the slicer with ‘Day’ values (0, 1, 2, 3), is there a way to show the corresponding ‘Date’ values instead? Unbeknownst to the end user, they see only the “dates” but actually slicing the ‘Day’ values, which makes it easy for me so I can keep the default slicer to 2 ‘Day’ values (0 and 1).

Workaround Solution (Slicer Override): Have 2 slicers, one for ‘Day’ column and the other for ‘Date’ column, then have my pre-selected ‘Day’ slicer hidden from view. The ‘Date’ slicer is visible and have all 4 dates available that can override the ‘Day’ slicer. I tried to do this using edit interactions, but it doesn’t work that day.

Unpopular Workaround ('Day' value under 'Date' value): Not ideal and haven’t had a chance to fully test it but sounds like it’ll work. Add the ‘Date’ column as a slicer value first, then ‘Day’ column, which becomes acts as the value underneath the ‘Date’ value, then pre-select my ‘Day’ values. The user sees a dropdown arrow for each date, but at least sees the dates.

 

Question: Based off my problem, are either of the first two solutions presented feasible and if so, how? If not, am I stuck trying the third unpopular workaround or do you recommend another method? Again, screenshot and PBIX file link below.

 

Comment: Since my report is through Direct Query mode, I’ve heard others responding to a problem like mines and said to create a separate non-related table, setup a uni-directional relationship cardinality from the non-related table to the ‘Day’ column, then slice against the non-related table. Not sure if that’s the only solution or is there a measure that can cleverly display ‘Date’ values for ‘Day’ values…Again, thank you in advance.

 

Public OneDrive of PBIX file: Slicer Value Reference or Overriding Slicer.pbix

 

Need Help

3 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    kylifeofpy,

     

    Try creating a date table as shown below. This is a DAX calculated table, but you can also create a date table in Power Query. You can create as many fields as you like (I included a few extra ones as examples).

     

    DimDate = 
VAR vStartDate =
    DATE ( 2023, 1, 1 )
VAR vEndDate =
    DATE ( 2024, 12, 31 )
VAR vToday = 
    TODAY()
VAR vResult =
    ADDCOLUMNS (
        CALENDAR ( vStartDate, vEndDate ),
        "Year", YEAR ( [Date] ),
        "Month Number", MONTH ( [Date] ),
        "Month Name", FORMAT ( [Date], "mmmm" ),
        "Day", DATEDIFF ( [Date], vToday, DAY ),
        "Day and Date", DATEDIFF ( [Date], vToday, DAY ) & " | " & [Date] 
    )
RETURN
    vResult

     

     

    Create a relationship between the tables:

     

     

    Create a slicer using a DimDate field. This will filter the IdentificationBadge table.

    • kylifeofpy's avatar
      kylifeofpy
      Frequent Visitor
      2 years ago

      DataInsights Thanks for the calculated table, is this for the slicer override option where the DimDate's slicer overrides the 'Day' slicer that's preselected to 0 and 1? I tried this option and disabled the interactions to not have the 'Day' slicer selection interact with the 'DimDate-Date' slicer, then select dates not related to 0 (11/06) nor 1 (11/05), but no visuals display (screenshot below). Perhaps I'm misunderstanding what the DimDate is supposed to do.

       

      Another option presented was to use mixed mode by creating a calculated table that's local storage, which then refers to the 'Day' and 'Date' columns from the Direct Query's 'IdentificationBadge' table (in the real model I would use, I know the shared sample dataset is Import), then I thought, would it work if I create a 1:M relationship between the 'local table'[Day] column to the 'IdentificationBadge'[Day] column for filtering. Then create a slicer from the 'local table'[Date] column in an attempt to "override" the 'IdentificationBadge'[Day] slicer. Do you know if that's even a feasible solution? Not even sure how to approach this using DAX but just thinking through this conceptually.

       

       

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        2 years ago

        kylifeofpy,

         

        The idea is to create a slicer using any DimDate field, and filter the fact table IdentificationBadge accordingly. The slicer could use Day, Date, or a concatenation of Day and Date from DimDate (whatever is most intuitive for users). Only one slicer would be needed, so there wouldn't be a need to override.