Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Helper II
2 years ago
Solved

Need Support in Problem approaching

Hi  amustafa ,

 

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Support-in-Complex-Dax-and-Visualization-filters/m-p/3626705

 

Do you have any idea about this. 

 

Please help me in this.

 

Thanks you.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Navaneetharaju_ 

    You can try the following solution.

    1.Create a time column in original table

     

    Time bins = HOUR([start_time])&"-"&HOUR([start_time])+1

     

    2.Create a time_bin table

     

    Time_bin = SUMMARIZE(ADDCOLUMNS(GENERATESERIES(0,23),"Time bins",[Value]&"-"&[Value]+1),[Time bins])

     

    3.Create a relationship among the original table and the time_bin table

    the table relationships

    4.Create a measure

     

    Measure =
VAR a =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Counts",
            VAR _days =
                GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] )
            VAR _weeks =
                GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 7 )
            VAR _months =
                SUMMARIZE (
                    ADDCOLUMNS (
                        GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 30 ),
                        "Dates", DATE ( YEAR ( [Value] ), MONTH ( [Value] ), DAY ( [start_date] ) )
                    ),
                    [Dates]
                )
            VAR _years =
                GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 365 )
            RETURN
                SWITCH (
                    TRUE (),
                    [repeat_by] = "days", COUNTROWS ( INTERSECT ( _days, VALUES ( 'Date'[Date] ) ) ),
                    [repeat_by] = "weeks", COUNTROWS ( INTERSECT ( _weeks, VALUES ( 'Date'[Date] ) ) ),
                    [repeat_by] = "months", COUNTROWS ( INTERSECT ( _months, VALUES ( 'Date'[Date] ) ) ),
                    [repeat_by] = "year", COUNTROWS ( INTERSECT ( _years, VALUES ( 'Date'[Date] ) ) )
                )
    )
RETURN
    SUMX ( FILTER ( a, [Time bins] IN VALUES ( Time_bin[Time bins] ) ), [Counts] )

     

    Output

     

    Best Regards!

    Yolo Zhu

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

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Navaneetharaju_ 

    Did you use a date table to as dimension tables, and based on your information, the time bins needed to be created or it is an existed condion? and if you want to achieve the chart visual, which field you want to put in the x-asix, and which field you want to calculate to to put to the y-aixs?

     

    Best Regards!

    Yolo Zhu

     

    • Navaneetharaju_'s avatar
      Navaneetharaju_
      Helper II
      2 years ago

      Hi Anonymous ,

      We have a date dim table.

      X axis should be - time groups

      Y axis - count of tasks on that selected date on particular time

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Navaneetharaju_ 

        You can try the following solution.

        1.Create a time column in original table

         

        Time bins = HOUR([start_time])&"-"&HOUR([start_time])+1

         

        2.Create a time_bin table

         

        Time_bin = SUMMARIZE(ADDCOLUMNS(GENERATESERIES(0,23),"Time bins",[Value]&"-"&[Value]+1),[Time bins])

         

        3.Create a relationship among the original table and the time_bin table

        the table relationships

        4.Create a measure

         

        Measure =
VAR a =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Counts",
            VAR _days =
                GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] )
            VAR _weeks =
                GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 7 )
            VAR _months =
                SUMMARIZE (
                    ADDCOLUMNS (
                        GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 30 ),
                        "Dates", DATE ( YEAR ( [Value] ), MONTH ( [Value] ), DAY ( [start_date] ) )
                    ),
                    [Dates]
                )
            VAR _years =
                GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 365 )
            RETURN
                SWITCH (
                    TRUE (),
                    [repeat_by] = "days", COUNTROWS ( INTERSECT ( _days, VALUES ( 'Date'[Date] ) ) ),
                    [repeat_by] = "weeks", COUNTROWS ( INTERSECT ( _weeks, VALUES ( 'Date'[Date] ) ) ),
                    [repeat_by] = "months", COUNTROWS ( INTERSECT ( _months, VALUES ( 'Date'[Date] ) ) ),
                    [repeat_by] = "year", COUNTROWS ( INTERSECT ( _years, VALUES ( 'Date'[Date] ) ) )
                )
    )
RETURN
    SUMX ( FILTER ( a, [Time bins] IN VALUES ( Time_bin[Time bins] ) ), [Counts] )

         

        Output

         

        Best Regards!

        Yolo Zhu

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