Dynamic Filter - Date/Time

[leandroparischi]

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 you have a DATE selected, then the TIME filter needs to show the time for that day.

Is there a way to do this?

[v-zhenbw-msft]

Hi @leandroparischi ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft · Accepted solution]

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.

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]))

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

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

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.

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.

[amitchandak]

@leandroparischi , Can you share sample data and sample output in table format?

Create time table: https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Greg_Deckler]

@leandroparischi Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.