Forum Discussion

EFI's avatar
EFI
Icon for Helper I rankHelper I
1 year ago
Solved

Slicer with days AND hours ?

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 s...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi EFI ,

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

 

 

Best Regards,
Wenbin Zhou

EFI's avatar
EFI
Icon for Helper I rankHelper I
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      EFI
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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