Dynamic Slicer & Histogram

[j_gan2022]

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 ID	Attendance Date
1	20/Jan/2022
2	20/Jan/2022
1	20/Jan/2022
2	21/Jan/2022
2	21/Jan/2022
3	21/Jan/2022
3	21/Jan/2022
4	22/Jan/2022
3	22/Jan/2022
4	22/Jan/2022

[j_gan2022]

Need help on this. Thanks

[v-chenwuz-msft · Accepted solution]

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.

[amitchandak]

@j_gan2022 , There is a histogram visual, have you tried that. Search on app store. There should be one from Microsoft , try that.

A calculated table can not use slicer values.

Better to create dynamic segmentation

https://www.daxpatterns.com/dynamic-segmentation/

[j_gan2022]

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