Forum Discussion

j_gan2022's avatar
j_gan2022
Frequent Visitor
4 years ago
Solved

Dynamic Slicer & Histogram

Hi I wanted to create a histogram that is dynamic changes based on my slider.

The Histogram shows the distribution of attendances of students based on Last X Days(Slicer).
The Attendance date for a student can be duplicated for a single date (different course).

I can do it based on a fixed duration. eg Last 30 days.

 

1) SUMMARIZE(MY_TABLE, MY_TABLE[Student_ID], "Number of Attendance", COUNTROWS(MY_TABLE))

2) Create visualization based on the count of counts.

 

 

however I can't use a what-if slicer to dynamically change my table based on X Days in step 1)

Your advise is appreciated.

This is my data.

Student IDAttendance Date
120/Jan/2022
220/Jan/2022
120/Jan/2022
221/Jan/2022
221/Jan/2022
321/Jan/2022
321/Jan/2022
422/Jan/2022
322/Jan/2022
422/Jan/2022
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi j_gan2022 

     

    The first thing to note is that tables created by new table cannot interact with the slicer in the page, in other words, the table is created statically.

    In other words, the table is created statically, so we can only create tables in measure to make them dynamic.

    The first step is to prepare the x-axis and what if slicer (last n day table)

    X-axis = GENERATESERIES(0,200)

    The second step is to create a measure like the one below.

    Measure =
VAR _what_if_n_day =
    SELECTEDVALUE( 'Last n day'[Last n day] )
VAR _last_day =
    TODAY()
VAR _last_n_day_table =
    FILTER( 'MY_TABLE', [Attendance Date] >= _last_day - _what_if_n_day )
VAR _summarize =
    SUMMARIZE(
        _last_n_day_table,
        [Student ID],
        "Number of Attendance", COUNTROWS( 'MY_TABLE' )
    )
RETURN
    COUNTROWS(
        FILTER(
            _summarize,
            [Number of Attendance] = SELECTEDVALUE( 'X-axis'[Value] )
        )
    )

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

4 Replies

    • j_gan2022's avatar
      j_gan2022
      Frequent Visitor
      4 years ago

      Hi amitchandak , I have completed on the histogram.
      However, I couldnt understand the dynamic segmentation. Could you give a starting code for me to follow up? Thanks

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      Hi j_gan2022 

       

      The first thing to note is that tables created by new table cannot interact with the slicer in the page, in other words, the table is created statically.

      In other words, the table is created statically, so we can only create tables in measure to make them dynamic.

      The first step is to prepare the x-axis and what if slicer (last n day table)

      X-axis = GENERATESERIES(0,200)

      The second step is to create a measure like the one below.

      Measure =
VAR _what_if_n_day =
    SELECTEDVALUE( 'Last n day'[Last n day] )
VAR _last_day =
    TODAY()
VAR _last_n_day_table =
    FILTER( 'MY_TABLE', [Attendance Date] >= _last_day - _what_if_n_day )
VAR _summarize =
    SUMMARIZE(
        _last_n_day_table,
        [Student ID],
        "Number of Attendance", COUNTROWS( 'MY_TABLE' )
    )
RETURN
    COUNTROWS(
        FILTER(
            _summarize,
            [Number of Attendance] = SELECTEDVALUE( 'X-axis'[Value] )
        )
    )

       

      Pbix in the end you can refer.

      Best Regards

      Community Support Team _ chenwu zhu

       

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