Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
leandroparischi
Frequent Visitor

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

Is there a way to do this?

 

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

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
Community Support
Community Support

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.

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.