Forum Discussion

Reddy5833's avatar
Reddy5833
Helper II
1 year ago

Filter dates in X axis dynamically in a column chart based on a date slicer selection

Hi,

 

Can someone help me with below requirement please.

 

If user choose any date range of 1-4 days: the column chart should Show the data for last 10 days (10 columns in the column chart, one column for each day). likewise, If user choose 5-10 days: show the last 15 days.
If user choose 11 days or more: Directly reflect the selected range.

 

I have a "Entries" fact table as below

Entry ID

Entry date

Machine ID

E1

01-Jan-24

M1

E2

01-Jan-24

M6

E3

01-Jan-24

M2

E4

02-Jan-24

M1

E5

03-Jan-24

M3

E6

04-Jan-24

M8

E7

04-Jan-24

M1

E8

04-Jan-24

M1

E9

05-Jan-24

M4

E10

06-Jan-24

M5

 

I need a line with column chart where distinct count of machine Id’s should be displayed in the column and count of entries be displayed in the line chart, the only thing is I want to control the displayed date rage in the x axis based on the selected date rage.
 

I have tried to achieve this in couple of ways like using connected date dimension and another way is using the disconnected date table as well. but didn't work.

 

Below is the dax measures I have used.
To count the number of days selected: 

DaysSelected =

VAR MinSelectedDate = MIN('DateTable'[Date])

VAR MaxSelectedDate = MAX('DateTable'[Date])

RETURN

DATEDIFF(MinSelectedDate, MaxSelectedDate, DAY) + 1


and the main measure I used in column Y axis is

Dynamic Entry Count =

 

VAR MaxFactDate = MAX(Entries[Entry Date])

VAR SelectedMinDate = MIN('DateTable'[Date])

VAR SelectedMaxDate = MAX('DateTable'[Date])

VAR DaysRange = [DaysSelected]

 

RETURN

SWITCH(

    TRUE(),

    DaysRange <= 4,

    CALCULATE(

            DISTINCTCOUNT('Entries'[Machine ID]),

            Entries[Entry Date] >= MaxFactDate - 9 && Entries[Entry Date] <= MaxFactDate

        ),

    DaysRange > 4 && DaysRange <= 10,

    CALCULATE(

            DISTINCTCOUNT('Entries'[Machine ID]),

            Entries[Entry Date] >= MaxFactDate - 14 && Entries[Entry Date] <= MaxFactDate

        ),

    DaysRange > 10,

        CALCULATE(

            DISTINCTCOUNT('Entries'[Machine ID]),

            Entries[Entry Date] >= SelectedMinDate && Entries[Entry Date] <= SelectedMaxDate

        )

)

 

 

Please help me to achive this.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Reddy5833 ,

     

    Thanks for reaching out to our community.

    After I reviewed your post, I deduce that the crux of the matter is the relationship that exists between the date table and the main table. If you want to keep the relationship, you can modify the measure as follows:

    Dynamic Entry Count =
VAR MaxFactDate =
    MAX ( Entries[Entry Date] )
VAR SelectedMinDate =
    MIN ( 'DateTable'[Date] )
VAR SelectedMaxDate =
    MAX ( 'DateTable'[Date] )
VAR DaysRange = [DaysSelected]
RETURN
    SWITCH (
        TRUE (),
        DaysRange <= 4,
            CALCULATE (
                DISTINCTCOUNT ( 'Entries'[Machine ID] ),
                FILTER (
                    ALL ( 'Entries' ),
                    Entries[Entry Date] >= MaxFactDate - 9
                        && Entries[Entry Date] <= MaxFactDate
                )
            ),
        DaysRange > 4
            && DaysRange <= 10,
            CALCULATE (
                DISTINCTCOUNT ( 'Entries'[Machine ID] ),
                FILTER (
                    ALL ( 'Entries' ),
                    Entries[Entry Date] >= MaxFactDate - 14
                        && Entries[Entry Date] <= MaxFactDate
                )
            ),
        DaysRange > 10,
            CALCULATE (
                DISTINCTCOUNT ( 'Entries'[Machine ID] ),
                FILTER (
                    ALL ( 'Entries' ),
                    Entries[Entry Date] >= SelectedMinDate
                        && Entries[Entry Date] <= SelectedMaxDate
                )
            )
    )

    I add ALL function to ignore the fitlering of dates. Hope it helps.

     

    Best Regards,

    Stephen Tao

     

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

     

    • Reddy5833's avatar
      Reddy5833
      Helper II
      1 year ago

      Hi Anonymous, thanks for the reply, 

      It is still not working, the cart is showing the data for all the dates as we are using 'All' function, but my requiremetn is different: it is as described belwo

       

      For example, let’s say max entry date in my fact table is 09-Sep-2024, 

      if I choose the date range of 1-Jan-2024 to 4-Jan-2024 (4 days) from slicer, then it should display last 10 days on the x axis of the column chart that means from 31-Aug-2024 to 9-Sep-2024.

       

      similarly if the selected date range is more than 4 days (1-Jan-2024 to 5-Jan-2024) and <= 10 days (1-Jan-2024 to 10-Jan-2024), then we should show last 15 days ( 26-Aug-2024 to 9-Sep-2024) data in the chart.

      But if the selected date range is greater than 10 days (1-Jan-2024 to 11-Jan-2024) then show whatever the range selected by user, (show 1-Jan-2024 to 11-Jan-2024 data in the chart)

       

      Anonymous Sorry for the typo

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Reddy5833 ,

         

        Thanks for your reply. I can understand your needs now based on your new description. Your measures are actually pretty good, I made a simple sample of data and applied your two measures, and it successfully returned the correct results.

        See Entry Date data:

        Here's what the filter results for different filters.

         

        According to my reasoning, the reason for the error in measure return on your side is because there is a relationship between your Date Table and the Entry Date, please remove it.

         

         

        Best Regards,

        Stephen Tao

         

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