Slicer with days AND hours ?

[EFI]

Hello there,

I start creating a dashboard to monitor the energy comsumption in some of our plants and I encounter some issue with the timestemp / slicer, I try some workaround but couldn't find something working properly.

Is there a way to create an hour slicer ? (slicer is not mandatory of course, but I would want be able to "play" with hours a way or another).

I try to put an exemple below of what we would want, I hope its clear enough.

Also the .pbix file : https://drive.google.com/file/d/11m5hYVyLFd5XEfb1A4L2pIjWxpfpkh_Y/view?usp=sharing

Thank you.

[Anonymous]

Hi Thejeswar ,thanks for the quick reply, I'll add more.

Hi @EFI ,

For some reason I can't download the .pbix file you provided at the moment, but I think your timestamps should be about the same as the data I'm assuming.

Please follow these steps:

Create two columns, one for the date slicer and one for the hour slicer.

Time.Hour([DateTime]))

2.Creating a Date Dimension Table and an Hour Dimension Table

DateTable = CALENDAR(DATE(2024,1,1),DATE(2024,1,10))

HourTable = GENERATESERIES(0,24,1)

Final output

Best Regards,
Wenbin Zhou

[EFI]

Hi,

I will try to do a separate hour table and see how that work, thank you.

I try to re upload my .pbix from a non corporate goog drive account, can you try again and see if its work now ? : https://drive.google.com/file/d/1vbvg6udr8wUQbPG4BfDvG03MHUvQRNTo/view?usp=sharing

[Anonymous]

Hi @EFI ,

It should work fine.

Due to exceeding the file size limit for uploads, I will not upload it. The steps should not have any issues.

Best Regards,
Wenbin Zhou

[EFI]

Did you change any relation between the table from te original .pbix ?

I try it but keep getting blank on the graph.

Thank you for your support.

[Anonymous]

Hi @EFI ,

I created a relationship between the fact table and the hourly dimension table.Other unchanged

Best Regards,
Wenbin Zhou

[EFI]

@Anonymous Alright, its curious because its seems I do same thing as you but when I change the slicer, I get blank value every time.

PBIx : Solfcast/DI_sample

[Anonymous]

Hi @EFI ,

I found the cause of the problem.Column 'timestamputc' contains hour-minute part, which results in only “mm-dd-yyyyy 12:00:00AM” data being filtered.

Best Regards,
Wenbin Zhou

[EFI]

Oh right, didn't see I was still on the timestemp with the hour included.

Its great its moving forward thanks, but its more complex than that in fact :c

This is giving me the sum for every day between 10 and 12.

The goal was more to have a start hour and end hour and looking for all value between them.

I hope im clear enough with my paint skill 🙂

[Anonymous]

Hi @EFI ,

Correct me if I'm misunderstanding.

1.Delete the relationship between the hourly dimension table and the fact table

2.Use the following DAX expression to create a measure

MEASURE = 
VAR _startHour =
    MIN ( 'Hour'[Hour] )
VAR _endHour =
    MAX ( 'Hour'[Hour] )
VAR _startDate =
    MIN ( 'Calendar'[Date] )
VAR _endDate =
    MAX ( 'Calendar'[Date] )
VAR _curren_x_axis_date_hour =
    SELECTEDVALUE ( ENERGY[timestamputc] )
VAR _curren_x_axis_date =
    SELECTEDVALUE ( ENERGY[timestamputc - Date] )
VAR _result =
    IF (
        _curren_x_axis_date = _startDate
            && HOUR ( _curren_x_axis_date_hour ) >= _startHour,
        SUM ( ENERGY[value] ),
        IF (
            _curren_x_axis_date = _endDate
                && HOUR ( _curren_x_axis_date_hour ) <= _endHour,
            SUM ( ENERGY[value] ),
            IF (
                _curren_x_axis_date <> _startDate
                    && _curren_x_axis_date <> _endDate,
                SUM ( ENERGY[value] )
            )
        )
    )
RETURN
    _result

3.Final output

Best Regards,
Wenbin Zhou

[EFI]

Hi, yes exacly, I was looking for something like this !

Can I ask you one more question, so its working perfect If I want to seach for exemple from 10 to 12, but could it work if I want to display from 23 to 09 ?

I think the slicer not letting me put superior value first.

Thank you very much for your support.

[Anonymous · Accepted solution]

Hi @EFI ,

Regarding your question, 'Between' style slicers can only have values from small to large by default. How about creating two slicers?

I added a rectangle to cover the excess slicer section.

MEASURE = 
VAR _startHour =
    MIN ( 'BeginHour'[Hour] )
VAR _endHour =
    MAX ( 'EndHour'[Value] )
VAR _startDate =
    MIN ( 'Calendar'[Date] )
VAR _endDate =
    MAX ( 'Calendar'[Date] )
VAR _curren_x_axis_date_hour =
    SELECTEDVALUE ( ENERGY[timestamputc] )
VAR _curren_x_axis_date =
    SELECTEDVALUE ( ENERGY[timestamputc - Date] )
VAR _result =
    IF (
        _curren_x_axis_date = _startDate
            && HOUR ( _curren_x_axis_date_hour ) >= _startHour,
        SUM ( ENERGY[value] ),
        IF (
            _curren_x_axis_date = _endDate
                && HOUR ( _curren_x_axis_date_hour ) <= _endHour,
            SUM ( ENERGY[value] ),
            IF (
                _curren_x_axis_date <> _startDate
                    && _curren_x_axis_date <> _endDate,
                SUM ( ENERGY[value] )
            )
        )
    )
RETURN
    _result

Best Regards,
Wenbin Zhou

[EFI]

Great, I can workaround like this, thank you very much for your time and help on this topic @Anonymous 

[Thejeswar]

Hi @EFI ,

You can try using independent time tables for the hour slicer. with each table driving a slicer and these tables filtering the main table

Regards,

[EFI]

Hi,

You mean like a generic table with hour, like a calendar table ?

[Thejeswar]

@EFI ,

Yes something like that. But this table should be connected to the main fact table. Try being a little innovative on where to have an active / inactive relatonship. 

Regards,