Date Slicer

[Amyrie]

Hello everyone, I have a case like this: 

I have a 2 tables: 

- Table 1: named "Dile_fact"

Name	Date_exam	Date_release	Units
Ava	01/15/24	11/01/24	Meter (m)
Leo	02/20/24	11/15/24	Kilometer (km)
Mia	03/10/24	12/05/24	Centimeter (cm)
Zoe	04/25/24	12/25/24	Millimeter (mm)
Jaxon	05/30/24	01/30/24	Inch (in)
Clara	07/04/24	02/14/24	Kilogram (kg)
Asher	08/21/24	03/22/24	Gram (g)
Nia	09/15/24	04/07/24	Pound (lb)
Cate	03/29/24	05/16/24	Ounce (oz)
Silas	09/15/24	06/30/24	Liter (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!

[Kedar_Pande]

@Amyrie 

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

[rajendraongole1]

Hi @Amyrie - 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]

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]

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

Hi, @Amyrie  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.