Forum Discussion

leandroparischi's avatar
leandroparischi
Frequent Visitor
6 years ago
Solved

Dynamic Filter - Date/Time

Hello! I need help making a dynamic filter using data segmentation: I have a DATA field and a TIME field. I need the TIME field to be blank in the segmentation if there is no DATE selected. If...
  • v-zhenbw-msft's avatar
    6 years ago

    Hello @leandroparischi ,

    Sorry that's why Power BI doesn't support time segmentation being blank if no DATE is selected in date segmentation.

    You may be able to create a slicer usingthe date field and place the Time field in Filter. When you select a date, you can select Filter Time.

    D1.jpg

    Or you can try the following shapes, putting the Time in a table visual.

    1. Create a Date table and a Time table.

    Date table = CALENDAR("2020/1/1","2020/12/31")

    TimeTable = 
    VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])
    VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])
    VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Second", [Value]) 
    RETURN
    ADDCOLUMNS(
    CROSSJOIN(HourTable, MinuteTable, SecondsTable),
    "Time", TIME([Hour], [Minute], [Second]))

    D2.jpg

    2. Then create a range column in the Time table.

    Rank = RANKX(TimeTable,TimeTable[Time],,ASC,Dense)

    D3.jpg

    3. Then we can create a measure, put the measurement and range in a visual table.

    Time measure = 
    IF(
        ISFILTERED('Date table'[Date]),MAX(TimeTable[Time]),BLANK())

    4. If we do not select the date segmentation, the measurement is blank, if we select the date, the measurement will be displayed all the time. Next, we can select the time to filter another table.

    D4.jpg

    D5.jpg

    D6.jpg

    If you don't meet your requirements, could you show the exact expected result based on the table we shared?

    Best regards

    Community Support Team _ zhenbw

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


    BTW, pbix as attached.