Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Date Slicer

Hello everyone, I have a case like this: 

I have a 2 tables: 

- Table 1: named "Dile_fact"

NameDate_examDate_releaseUnits
Ava 01/15/2411/01/24Meter (m)
Leo02/20/2411/15/24Kilometer (km)
Mia03/10/2412/05/24Centimeter (cm)
Zoe04/25/2412/25/24Millimeter (mm)
Jaxon05/30/2401/30/24Inch (in)
Clara07/04/2402/14/24Kilogram (kg)
Asher08/21/2403/22/24Gram (g)
Nia09/15/2404/07/24Pound (lb)
Cate03/29/2405/16/24Ounce (oz)
Silas09/15/2406/30/24Liter (L)

- Table 2: dim_date (for reference)

dim_date =
VAR StartDate = DATE(2024, 1, 1)
VAR EndDate = DATE(2024, 12, 31)
RETURN
    ADDCOLUMNS(
        CALENDAR(StartDate, EndDate),
        "Year", YEAR([Date]),
        "Month", FORMAT([Date], "MMMM"),
        "Month Number", MONTH([Date]))
* 2 tables above connected via Date = Date_Exam
I want to create 2 slicers: 1st "Date_type" (include Date_exam, Date_release), 2nd "Time_range" (get Date column in dim_date table)(dropdown value list)
So if I choose Data_type = "Date_exam" and time_range = "01/15/24", my expect Dile_fact result will display row has Date_exam = "01/15/24"
 

Could someone please help me how to implement it? As a newcomer to Power BI, I would greatly appreciate your assistance. Thank you!

Need Help
Tips and Tricks

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Anonymous - Create a disconnected table named DateType (Calculated table) to hold the options “Date_exam” and “Date_release”

     

    DateType =
    DATATABLE(
    "Date_Type", STRING,
    {
    {"Date_exam"},
    {"Date_release"}
    }
    )

     

    Add a slicer visual to your report.
    Set the slicer to use the DateType[Date_Type] column.
    Name this slicer “Date_type.”

    Now create a measure as below:

     

    FilterDileFact =
    VAR SelectedDateType = SELECTEDVALUE(DateType[Date_Type])
    VAR SelectedTimeRange = SELECTEDVALUE(dim_date[Date])
    RETURN
    IF(
    (SelectedDateType = "Date_exam" && SELECTEDVALUE(Dile_fact[Date_exam]) = SelectedTimeRange) ||
    (SelectedDateType = "Date_release" && SELECTEDVALUE(Dile_fact[Date_release]) = SelectedTimeRange),
    1,
    0
    )

     

    Select the table or other visuals displaying data from Dile_fact.Drag the FilterDileFact measure to the Filters on this visual pane.Set the filter condition to show only when FilterDileFact is equal to 1.

    Hope the process works as expected.

     

     

     

    • Amyries's avatar
      Amyries
      Helper I
      1 year ago

      Thank you for your response. But seems like it's not working.
      My expectation is, to select Date_exam and time range 1/15/2024-1/17/2024. The table chart only displays 1 row satisfied with the slicer (yellow row)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the reply from Kedar_Pande  and rajendraongole1 , please allow me to provide another insight:

        Hi, Anonymous  and Amyries 

        Regarding the issue you raised, my solution is as follows:

        1.Firstly, if you need to use the time from dim_date as a slicer, I recommend disconnecting the links between the two tables. Otherwise, the slicer will directly affect the other two tables.

        2.Secondly, similar to rajendraongole1 , create a calculated table to serve as the dimension slicer.

        DateType = 
DATATABLE(
"Date_Type", STRING,
{
{"Date_exam"},
{"Date_release"}
}
)

         

        3.Next, apply the following measures as filters in the visual object:

        MEASURE = 
VAR cc1 =
    DISTINCTCOUNT ( 'DateType'[Date_Type] )
VAR type1 =
    VALUES ( 'DateType'[Date_Type] )
VAR time1 =
    VALUES ( 'dim_date'[Date] )
RETURN
    IF (
        ISFILTERED ( 'DateType'[Date_Type] ),
        IF (
            cc1 = 1,
            IF (
                (
                    "Date_exam"
                        IN type1
                            && MAX ( 'Dile_fact'[Date_exam] ) IN time1
                )
                    || (
                        "Date_release"
                            IN type1
                                && MAX ( 'Dile_fact'[Date_release] ) IN time1
                    ),
                1,
                0
            ),
            IF (
                MAX ( 'Dile_fact'[Date_exam] )
                    IN time1
                        && MAX ( 'Dile_fact'[Date_release] ) IN time1,
                1,
                0
            )
        ),
        1
    )

        4.Finally, here are the results, which I hope will meet your needs:

         

         

        Please find the attached pbix relevant to the case.

         

        Best Regards,

        Leroy Lu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Anonymous 

    Create a new table for Date Types:

    DateType = DATATABLE("Date Type", STRING, {{"Date_exam"}, {"Date_release"}})

    Create a measure to filter based on selected values

    Filtered Result =
    VAR SelectedDateType = SELECTEDVALUE(DateType[Date Type])
    VAR SelectedDate = SELECTEDVALUE(dim_date[Date])

    RETURN
    IF(
    SelectedDateType = "Date_exam",
    CALCULATE(
    COUNTROWS(Dile_fact),
    Dile_fact[Date_exam] = SelectedDate
    ),
    IF(
    SelectedDateType = "Date_release",
    CALCULATE(
    COUNTROWS(Dile_fact),
    Dile_fact[Date_release] = SelectedDate
    )
    )
    )

    Use the Filtered Result measure in the table to see the filtered count based on the slicer selections.

    💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn